MySQL delete row from multiple tables

Question

Is this the correct way to do it?

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'

It looks like you're trying to delete a row from multiple tables where each row shares a common ID.. is that correct? — John Weldon, Feb 06 '10 at 19:42
Yes that's exactly what I am trying to do . sorry for not adding that in the description — salmane, Feb 06 '10 at 19:46
Well have you run it? Either do this and ask when it does not work or check the manual: http://dev.mysql.com/doc/refman/5.5/en/delete.html — Felix Kling, Feb 06 '10 at 19:50
Are you just trying to do a cascading delete for a normalized db? — Joe Phillips, Feb 06 '10 at 19:59
Why do you need to test both the username and the id in your where clause? Is the id not unique? — Mark Byers, Feb 06 '10 at 20:05

score 15 · Accepted Answer · answered Feb 06 '10 at 20:08

15

Yes, that is correct. It works fine 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's not working for you, perhaps you can modify this example to show what problem you are having.

answered Feb 06 '10 at 20:08

Mark Byers

811,555
193
1,581
1,452

Is there a difference between what you posted ( i tried) and the answer posted above? :" Delete 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 so, which one is better? thank you – salmane Feb 06 '10 at 20:14
@salmane: Yeah, the main difference is that other answer doesn't work at all (try it - it gives a syntax error). He's changed it now though. – Mark Byers Feb 06 '10 at 20:21
1

The real way to say thank you is to mark this question as answered and giving credit to Mark. – Jay Askren Feb 06 '10 at 20:54
This only works if `id = 1` exists in _all four_ tables. If you wish to delete the row from any table that _might_ have a `id = 1`, then you cannot accomplish that via a single delete statement. – Kevin Cantwell Sep 29 '15 at 15:36

Jay Askren · Answer 2 · 2010-02-06T20:17:05.473

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

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 results you expect, just replace the *Select ** with Delete and your table names. Then it would become:

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'

score 1 · Answer 3 · edited Nov 22 '16 at 17:33

1

Make it simple with:

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

Enjoy :)

edited Nov 22 '16 at 17:33

Angel Politis

10,955
14
48
66

answered Jun 26 '14 at 14:58

Nadeem

419
4
13

MySQL delete row from multiple tables

3 Answers3

Linked

Related