MySQL: Delete row if id doesn't exist in other table

Question

I think this is newbie ask. How to delete row if id doesn't exist in other table? My thought is fetch the ids first and delete. I'm looking for better querying.

MyISAM or Innodb tables? This changes the response considerably. — 禪師無
– 禪師無, Commented Jul 29, 2011 at 8:22

genesis · Accepted Answer · 2011-07-29 08:06:36Z

11

You can set foreign keys to these tables.

More info here

for simple query, here it is:

 DELETE FROM table WHERE (SELECT count(1) FROM table2 WHERE id = table.id) < 1

answered Jul 29, 2011 at 8:06

genesis

51.1k20 gold badges99 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Liam Over a year ago

I also found this useful - thank you. I knew it would be a subquery, but I couldn't think how to write it. Edit, for those interested I'm using MySQL.

Yogster Over a year ago

Is there any reason why you used COUNT? That query will have to go through all of table2's rows to count how many occurrences of id there are. Using EXISTS would have been a lot more efficient as the query would stop as soon as it finds the first ocurrence: DELETE FROM table WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE id = table.id)

Collectives™ on Stack Overflow

MySQL: Delete row if id doesn't exist in other table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related