Mysql - delete multiple tables

Question

DELETE a,b FROM a AS a INNER JOIN b AS b ON a.b_id = b.id WHERE a.id IN (1,2,3,4) 

In the above query, only 1 record is deleted, why? I want to delete all 4 entries

Answer 1

I created a test sample as follows:

use test
TABLE DROP IF EXISTS a;
TABLE DROP IF EXISTS b;
CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
CREATE TABLE b (id INT NOT NULL PRIMARY KEY);
INSERT VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO b VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
SELECT * FROM a;
SELECT * FROM b;
REMOVE a. * FROM INNER JOIN b ON a.id = b.id WHERE a.id IN (1,2,3,4);
SELECT * FROM a;

Here are my results:

lwdba @localhost (DB test) :: using the test
Database changed
lwdba @localhost (DB test) :: DROP TABLE IF EXISTS a;
Request OK, 0 rows affected (0.06 sec)

lwdba @localhost (DB test) :: DROP TABLE IF EXISTS b;
Request OK, 0 rows affected (0.06 sec)

lwdba @localhost (DB test) :: CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
Request OK, 0 rows affected (0.23 s)

lwdba @localhost (DB test) :: CREATE TABLE b (id INT NOT NULL PRIMARY KEY);
Request OK, 0 rows affected (0.17 s)

lwdba @localhost (DB test) :: INSERT IN VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10 );
Request OK, 10 rows affected (0.06 sec)
Entries: 10 duplicates: 0 Warnings: 0

lwdba @localhost (DB test) :: INSERT INTO b VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), ( ten);
Request OK, 10 rows affected (0.06 sec)
Entries: 10 duplicates: 0 Warnings: 0

lwdba @localhost (DB test) :: SELECT * FROM a;
+ ---- +
| id |
+ ---- +
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+ ---- +
10 lines per set (0.00 sec)

lwdba @localhost (DB test) :: SELECT * FROM b;
+ ---- +
| id |
+ ---- +
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+ ---- +
10 lines per set (0.00 sec)

lwdba @localhost (DB test) :: DELETE a, b FROM INNER JOIN b ON a.id = b.id WHERE a.id IN (1,2,3,4); Request OK, 4 rows affected (0.08 sec)

lwdba @localhost (DB test) :: SELECT * FROM a;
+ ---- +
| id |
+ ---- +
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+ ---- +
6 lines per set (0.00 sec)

lwdba @localhost (DB test) :: SELECT * FROM b;
+ ---- +
| id |
+ ---- +
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+ ---- +
6 lines per set (0.00 sec)

Give it a try !!!

BTW I removed "AS a" and "AS b" from your request, and I changed a.b_id to a.id

Answer 2

DELETE command does not work with JOIN