Delete all rows in a table based on another table

Question

I can't seem to ever remember this query!

I want to delete all rows in table1 whose ID's are the same as in Table2.

So:

DELETE table1 t1
 WHERE t1.ID = t2.ID

I know I can do a WHERE ID IN (SELECT ID FROM table2) but I want to do this query using a JOIN if possible.

Answer 1

Try this:

DELETE Table1
FROM Table1 t1, Table2 t2
WHERE t1.ID = t2.ID;

or

DELETE Table1
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID;

Answer 2

DELETE Table1
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID

Answer 3

DELETE T1 
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID;

I always use the alias in the delete statment as it prevents the accidental

DELETE Table1

caused when failing to highlight the whole query before running it.

Answer 4

There is no solution in ANSI SQL to use joins in deletes, AFAIK.

DELETE FROM Table1
WHERE Table1.id IN (SELECT Table2.id FROM Table2)

Later edit

Other solution (sometimes performing faster):

DELETE FROM Table1
WHERE EXISTS( SELECT 1 FROM Table2 Where Table1.id = Table2.id)

Answer 5

Referencing MSDN T-SQL DELETE (Example D):

DELETE FROM Table1
FROM Tabel1 t1
   INNER JOIN Table2 t2 on t1.ID = t2.ID

Answer 6

I think that you might get a little more performance if you tried this

DELETE FROM Table1
WHERE EXISTS (
  SELECT 1
  FROM Table2
  WHERE Table1.ID = Table2.ID
)