CODEWITHSUNDEEP
sqlms-access
Sithelo
Sithelo

Reputation: 307

SQL Select with join query works but delete does not work

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.

Upvotes: 0

Views: 796

Answers (1)

John Woo
John Woo

Reputation: 263713

Try this:

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

Upvotes: 1

Related Questions