By salmane


2010-02-06 19:38:47 8 Comments

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'

3 comments

@Nadeem 2014-06-26 14:58:01

Make it simple with:

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

Enjoy :)

@Jay Askren 2010-02-06 19:51:11

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'

@Jay Askren 2010-02-06 20:17:40

Oops, your right. I corrected the answer.

@Mark Byers 2010-02-06 20:08:30

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.

@salmane 2010-02-06 20:14:41

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

@Mark Byers 2010-02-06 20:21:55

@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.

@salmane 2010-02-06 20:29:17

Thank You for your help :)

@Jay Askren 2010-02-06 20:54:44

The real way to say thank you is to mark this question as answered and giving credit to Mark.

@Kevin Cantwell 2015-09-29 15:36:15

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.

Related Questions

Sponsored Content

47 Answered Questions

37 Answered Questions

37 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

9 Answered Questions

[SOLVED] Skip certain tables with mysqldump

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

5 Answered Questions

[SOLVED] Deleting rows with MySQL LEFT JOIN

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

Sponsored Content