CODEWITHSUNDEEP
sqlsql-serverdatabasesql-deletedelete-row
DavidBoyd
DavidBoyd

Reputation: 3

DELETE clause deletes more rows than the SELECT subquery returns

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.

Upvotes: 0

Views: 1514

Answers (2)

justMe
justMe

Reputation: 2408

This also should work:

DELETE
FROM
    table1
WHERE
    EXISTS
    (
        SELECT
            *
        FROM
            table2
        WHERE
            table1.compositekey1 = table2.compositekey1
        AND table1.compositekey2 = table2.compositekey2
        AND table1.compositekey3 = table2.compositekey3 );

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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.

Upvotes: 2

Related Questions