Home ›
Delete from multiple tables that may have a one-to-many relationshipDelete from multiple tables that may have a one-to-many relationship
Submitted by Benjamin Melançon on July 9, 2007 - 2:49pm
NOTE: Conclusions here are provisional and input from a genuine SQL guru would be greatly appreciated.
SQL multiple table delete
Delete rows in three tables that share a key and satisfy a WHERE condition (in one of the tables). Table T3 will have a single row for each of these keys, as will table T2 for that matter, but table 1 will have multiple entries for each key.
The MySQL docs show up to two tables deleted, using three:
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
(This syntax does the same thing, but let's stick to the above, which is a little closer to English "DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;")
DELETE journal_t1, journal_t2
FROM journal_t1
LEFT JOIN journal_t2
ON journal_t1.key = journal_t2.key
WHERE journal_t1.key = '800001'
From:
http://dev.mysql.com/doc/refman/5.1/en/delete.html
http://dev.mysql.com/doc/mysql/comment.php?id=5721
So for what I need to do:
DELETE FROM t1, t2, t3 USING t1 LEFT JOIN t2 LEFT JOIN t3 WHERE t1.key = t2.key AND t2.key = t3.key
If your table names are longer than t1 you can provide table aliases in the from statement, and use that throughout.
Can't I just write "ON key" or something like that in place of the WHERE clause? That'd be so much prettier... ;-)
NOTE: Conclusions here are provisional and input from a genuine SQL guru would be greatly appreciated.
SQL multiple table delete
Delete rows in three tables that share a key and satisfy a WHERE condition (in one of the tables). Table T3 will have a single row for each of these keys, as will table T2 for that matter, but table 1 will have multiple entries for each key.
The MySQL docs show up to two tables deleted, using three:
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
(This syntax does the same thing, but let's stick to the above, which is a little closer to English "DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;")
DELETE journal_t1, journal_t2
FROM journal_t1
LEFT JOIN journal_t2
ON journal_t1.key = journal_t2.key
WHERE journal_t1.key = '800001'
From:
http://dev.mysql.com/doc/refman/5.1/en/delete.html
http://dev.mysql.com/doc/mysql/comment.php?id=5721
So for what I need to do:
DELETE FROM t1, t2, t3 USING t1 LEFT JOIN t2 LEFT JOIN t3 WHERE t1.key = t2.key AND t2.key = t3.key
If your table names are longer than t1 you can provide table aliases in the from statement, and use that throughout.
Can't I just write "ON key" or something like that in place of the WHERE clause? That'd be so much prettier... ;-)
Comments
If aliases are provided, their use is mandatory
From the MySQL docs:
" Note: If you provide an alias for a table, you must use the alias when referring to the table:
DELETE t1 FROM test AS t1, test2 WHERE ..."(Except for tables crossing databases, which cannot use aliases.)
SQL in use in Drupal
For the curious, this SQL in real life, in the Drupal module Community-managed Taxonomy:
Post new comment