DELETE removes less rows than it has to

Question

I have a task to delete rows in one table based on condition between two tables (I join on more than one column because there are no PRIMARY or UNIQUE KEYs:

DELETE rf 
FROM #tempTable rf 
LEFT JOIN #tempTableInc rfi 
    ON rf.ID = rfi.ID
    AND rf.Code = rfi.Code 
    AND rf.Type = rfi.Type
    AND rf.Year = rfi.Year
WHERE rfi.Value != rf.Value

If I only write this code:

SELECTrf.*, rfi.Value rfi
FROM #tempTable rf 
    LEFT JOIN #tempTableInc rfi 
        ON rf.ID = rfi.ID
        AND rf.Code = rfi.Code 
        AND rf.Type = rfi.Type
        AND rf.Year = rfi.Year
    WHERE rfi.Value != rf.Value

I receive for example 30 records. When I write it in the DELETE statement I delete only 26 records. What could be the reason for this disparity?

Answer 1

The reason for the discrepancy is that the JOIN might produce multiple rows for a give row in rf. Even though the SELECT returns those rows, only one is deleted.

Note that a LEFT JOIN is unnecessary for this logic, because the WHERE clause requires that there be a match, turning the outer join into an inner join.

You can get a more accurate count using EXISTS:

SELECT rf.*
FROM #tempTable rf 
WHERE EXISTS (SELECT 1
              FROM #tempTableInc rfi 
              WHERE rf.ID = rfi.ID AND
                    rf.Code = rfi.Code AND
                    rf.Type = rfi.Type AND
                    rf.Year = rfi.Year AND
                    rfi.Value <> rf.Value
             );