How to remove items that exist in one table from another?

Question

I have tables A and B. Elements of table B may also exist in table A, and I want to delete these elements. What do the SQL expressions look like for this?

Answer 1

this is an option

delete from a where a.key in (select key from b) 

Answer 2

Or:

 DELETE a WHERE a.some_field IN (SELECT some_field FROM b) 

or

 DELETE A WHERE EXISTS (SELECT 1 FROM b WHERE b.field1 = a.field2) 

Depending on your database, you may find that one works especially better than the other. IIRC Oracle prefers that WHERE IS THERE EXISTS IN, but this may depend on a number of factors.

Answer 3

Sort of:

 DELETE FROM TableA as A WHERE A.ID IN (SELECT ID FROM TableB AS B WHERE [your condition here]) 

Answer 4

In some databases, the rather exotic look of DELETE FROM FROM is very effective.

 delete from foo from foo as f where exists ( select 1 from bar as b where b.field = f.field ) 

Answer 5

If your tables use InnoDB, the easiest way would be to set table A with the foreign keys from table B and use ON DELETE CASCADE, thus, no code changes are required and the integrity of your database is guaranteed.

Answer 6

This is permitted by standard.

 delete a from a join b on a.id = b.id; 

Answer 7

 delete a --select a.* from tablea a join tableb b on a.someid = b.someid 

Make sure you first run the selection part to make sure you get the right entries.

Answer 8

 DELETE FROM FIRST_TABLE FT WHERE EXISTS( SELECT 1 FROM SECOND_TABLE ST WHERE ST.PRIMARY_KEY = FT.PRIMARY_KEY );