SQL Select with join query works but delete does not work

Question

I have two tables Table1 and Table2. I compared thes tables using the query below. SELECT 

Select Table1.ID
     FROM Table1
     LEFT OUTER JOIN Table2 
    ON Table1.ID =Table2.ID
     WHERE Table2.ID IS NULL

and got 1508 records that exist in Table1 but not in Table2. Now I wanted to delete these records in Table2. This is the code I used below

 DELETE Table1.*
    FROM Table1
     LEFT OUTER JOIN Table2 
    ON Table1.ID =Table2.ID
     WHERE Table2.ID IS NULL

This is the error I am get Could not delete from specified tables. I realize something is wrong with my sql, but where. I thought this DELETE was specifying a table not tables.

John Woo John Woo · Accepted Answer · 2012-10-17T08:07:38

1

votes

Try this:

DELETE FROM Table1
WHERE ID NOT IN
(
    SELECT ID
    FROM table2
)

SQL Select with join query works but delete does not work

1 Answers