DELETE clause deletes more rows than the SELECT subquery returns

Question

I have two tables with identical columns but a different amount of rows, the tables have a 3 column composite primary key.

There is table1 the original table, and table2 the updated table that has less rows because data was removed. I have a SELECT statement that returns the rows that are in table1, but not in table2; yet when I put the SELECT statement as a subquery in a DELETE clause using WHERE EXISTS it wants to delete all the rows from table 1, and not just the rows in the subquery.

Code:

DELETE FROM table1
WHERE EXISTS(
SELECT t1.*
FROM table1 AS t1 LEFT JOIN table2 AS t2
ON (t1.compositekey1 = t2.compositekey1)
AND (t1.compositekey2 = t2.compositekey2)
AND (t1.compositekey3 = t2.compositekey3)
WHERE (t2.compositekey1 IS NULL) AND 
(t2.compositekey2 IS NULL) AND (t2.compositekey3 IS 
NULL)
);

I tested the subquery as a standalone SELECT query and it returned 110 rows, the correct amount, but when put in the DELETE query above it wants to DELETE all 9600 rows. I was under the impression the WHERE EXISTS should remove only the rows that were in the virtual table returned by the subquery.

When I used the reverse query as an INSERT query, insert all the rows in table2 that aren't in table1 into table 1, it worked fine too.

So I don't know where I'm messing up on the DELETE statement.

I tried to use:

WHERE t1.compositekey1, t1.compositekey2, t1.compositekey3 IN (......)

But I get an error saying use EXISTS. This is being used in an access database so I guess the same rules apply as sql server.

Thanks in advance for any help.

Answer 1

Your subquery is not correlated and is returning at least one row. Hence exists always return true and the delete operation tries to deletes everything.

Try using not exists with a correlated subquery:

delete
from table1 t1
where not exists (
        select 1
        from table2 t2
        where t1.compositekey1 = t2.compositekey1
            and t1.compositekey2 = t2.compositekey2
            and t1.compositekey3 = t2.compositekey3
        );

You can do this using a left join too:

delete t1
from table1 t1
left join table2 t2 on t1.compositekey1 = t2.compositekey1
    and t1.compositekey2 = t2.compositekey2
    and t1.compositekey3 = t2.compositekey3
where t2.compositekey1 is null;

Also, I noticed that you were trying to check all three columns for null in the subquery. You need to check only one - any one.