MySQL multiple-table delete syntax

Question

I try to do 2 delete queries in one. For example:

DELETE FROM `comments` WHERE `article_id` = 4;
DELETE FROM `aricles` WHERE `id` = 4;

I tried using a single query:

DELETE `articles`, `comments` 
FROM `articles` INNER JOIN `comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4

That works well if in table comments exist records with article_id 4, but doesn't remove articles records with articles.id = 4, if in comments records with article_id = 4 not found. Is there any way to do that?

you really should take a look on constraints. see http://stackoverflow.com/questions/3433975/why-use-foreign-key-constraints-in-mysql — Rufinus, Mar 13 '12 at 11:37

score 4 · Accepted Answer · answered Mar 13 '12 at 11:53

4

This will do it -

DELETE `articles`, `comments`
FROM `articles`
LEFT JOIN `comments`
    ON `articles`.`id` = `comments`.`article_id`
WHERE `articles`.`id` = 4;

answered Mar 13 '12 at 11:53

user1191247

10,808
2
22
32

This will also delete `articles` that have no `comments`. This answers the question more exactly. – sulai Sep 13 '13 at 12:35

score 1 · Answer 2 · answered Mar 13 '12 at 11:44

1

Try the following:

DELETE FROM `articles`, `comments` 
USING `articles` INNER JOIN `comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4

answered Mar 13 '12 at 11:44

Korhan Ozturk

11,148
6
36
49

This will *not* delete `articles` that have no `comments`. – sulai Sep 13 '13 at 12:34

score 0 · Answer 3 · answered Feb 20 '15 at 05:07

Use this

DELETE FROM `articles`, `comments` 
USING `articles`,`comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4

or

DELETE `articles`, `comments` 
FROM `articles`, `comments` 
WHERE `comments`.`article_id` = `articles`.`id` AND `articles`.`id` = 4

MySQL multiple-table delete syntax

3 Answers3