Delete from multiple tables with common column in one query?

Question

Micro optimization I know, this is more because I am curious.

I have two link tables,

blog_categories_posts => post_id, category_id  
blog_tags_posts       => post_id, tag_id

I am removing all links from both tables based on the post_id:

DELETE FROM blog_categories_posts WHERE post_id = {$id};
DELETE FROM blog_tags_posts WHERE post_id = {$id};

Anyway, I am wondering if it is possible to delete from both tables at once, and whether it would be a performance (whilst micro) hit or gain?

I imagine if it was to work:

DELETE FROM blog_categories_posts, blog_tags_posts where post_id = {$id}

or a similar syntax?

There was another question like this I found, maybe it will help? http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql — Connor Hollis, Oct 07 '12 at 00:39

score 0 · Answer 1 · answered Jun 06 '13 at 15:45

The syntax is on the mysql website: Delete Syntax

DELETE FROM tbl1, tbl2
USING tbl1
JOIN tb2 USING (id)
WHERE id = :id

So for your example it would be:

DELETE FROM blog_categories_posts, blog_tags_posts
USING blog_categories_posts
JOIN blog_tags_posts USING (post_id)
WHERE post_id = $id

It's also very easy to add many tables:

DELETE FROM tbl1, tbl2, tbl3, tbl4, tbl5
USING tbl1
JOIN tb2 USING (id)
JOIN tb3 USING (id)
JOIN tb4 USING (id)
JOIN tb5 USING (id)
WHERE id = :id

1 Answers1