Delete all rows in a table based on another table

2018-06-25 09:31:31

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.

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

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

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

DELETE Table1

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

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)

链接地址: http://www.djcxy.com/p/71100.html