Coder Perfect

Remove all rows from a table that is based on another table.

Problem

I can’t seem to recall this question!

I’d like to delete all rows in Table 1 with the identical IDs as those in Table 2.

So:

DELETE table1 t1
 WHERE t1.ID = t2.ID

I know I can do a WHERE ID IN (SELECT ID FROM table2), but if feasible, I’d like to run this query with a JOIN.

Asked by mrblah

Solution #1

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

In the delete statement, I always use the alias since it prevents accidental deletion.

DELETE Table1 

When you don’t highlight the entire query before running it, you’ll get this error.

Answered by HLGEM

Solution #2

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

Answered by Stephen Mesa

Solution #3

AFAIK, there is no way to use joins in deletes with ANSI SQL.

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

Later edit

Another option (which can be faster in some cases):

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

Answered by Cătălin Pitiș

Solution #4

The following would be the PostgreSQL implementation:

DELETE FROM t1
USING t2
WHERE t1.id = t2.id;

Answered by Vesanto

Solution #5

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;

Answered by Yannick Motton

Post is based on https://stackoverflow.com/questions/1590799/delete-all-rows-in-a-table-based-on-another-table