MySQL removes a row from multiple tables

Question

Is this being done right?

DELETE t1, t2, t3, t4 FROM table1 as t1 INNER JOIN table2 as t2 on t1.id = t2.id INNER JOIN table3 as t3 on t1.id=t3.id INNER JOIN table4 as t4 on t1.id=t4.id WHERE t1.username='%s' AND t1.id='%s' 

Answer 1

Yes, that's right. It works great here:

 CREATE TABLE table1 (id int, username nvarchar(30)); CREATE TABLE table2 (id int); CREATE TABLE table3 (id int); CREATE TABLE table4 (id int); INSERT INTO table1 VALUES (1, 'Foo'),(2, 'Bar'); INSERT INTO table2 VALUES (1),(2); INSERT INTO table3 VALUES (1),(2); INSERT INTO table4 VALUES (1),(2); SELECT COUNT(*) FROM table1; 2 SELECT COUNT(*) FROM table2; 2 SELECT COUNT(*) FROM table3; 2 SELECT COUNT(*) FROM table4; 2 DELETE t1, t2, t3, t4 FROM table1 as t1 INNER JOIN table2 as t2 on t1.id = t2.id INNER JOIN table3 as t3 on t1.id=t3.id INNER JOIN table4 as t4 on t1.id=t4.id WHERE t1.username='Foo' AND t1.id='1'; SELECT COUNT(*) FROM table1; 1 SELECT COUNT(*) FROM table2; 1 SELECT COUNT(*) FROM table3; 1 SELECT COUNT(*) FROM table4; 1 

If it does not work for you, perhaps you can modify this example to show what your problem is.

Answer 2

An easy way to figure this out is to write it as a query first:

 SELECT * FROM table1 as t1 INNER JOIN table2 as t2 on t1.id = t2.id INNER JOIN table3 as t3 on t1.id=t3.id INNER JOIN table4 as t4 on t1.id=t4.id WHERE t1.username='%s' AND t1.id='%s' 

If you get the expected results, just replace * Select ** with Delete and your table names. Then it will be as follows:

 Delete t1, t2, t3, t4 From table1 as t1 INNER JOIN table2 as t2 on t1.id = t2.id INNER JOIN table3 as t3 on t1.id=t3.id INNER JOIN table4 as t4 on t1.id=t4.id WHERE t1.username='%s' AND t1.id='%s' 

Answer 3

Make it simple:

 DELETE FROM `Table1` t1, `Table2` t2 USING t1, t2 WHERE t1.`id` = t2.`id` AND t1.`id` = 10; <br> 

Enjoy :)