What's the fastest way to delete items that exist in one table in another?

Question

lets say i have tables a and b. b contains items that exist in table a. I want to be able to delete items in a that are in be. What would the sql look like?

Answer 1

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

make sure you run the selct part first to ensure that you are getting the records you want.

Answer 2

Something like:

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

Answer 3

this is an option

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

Answer 4

Either:

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 one works particularly better than the other. IIRC Oracle tends to prefer WHERE EXISTS to IN but this can depend on a number of factors.

Answer 5

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

Answer 6

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

Answer 7

In certain DBs the rather exotic looking DELETE FROM FROM is very efficient

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

Answer 8

This is allowed according to the standard.

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