CODEWITHSUNDEEP
sqlsql-server
Batman
Batman

Reputation: 8917

Delete statement not deleting all the records that I think should

I have two tables of data that I'm trying to merge into one consolidated table, according to a series of rules, based on where data is present, or absent. I start populating the table based on the first rule.

INSERT INTO CombinedData
SELECT a.matchingField,
    CASE
       WHEN a.FieldOne IS NULL
       THEN b.FieldOne
       ELSE a.FieldOne
    END as FieldOne,
    CASE
       WHEN a.FieldTwo IS NULL
       THEN b.FieldTwo
       ELSE a.FieldTwo
    END as FieldTwo,
a.FieldThree, b.FieldFour, a.DataDate
FROM TableA a
JOIN TableB b
ON a.matchingField = b.matchingField
AND a.DataDate = b.DataDate

After I've done this though, I want to remove the records from TableA and TableB that were used to populate the table. Partly because these tables are quite large, and this first criteria will cover ~90% of the records, so removing them will increase all the performance of the subsequent joins. More importantly though, having the records there will affect some the subsequent decision logic.

So, I try to delete those records:

DELETE a
FROM TableA a
INNER JOIN CombinedData cd
ON a.matchingField = cd.matchingField
AND a.DataDate = b.DataDate

However when I look at the number of records deleted, it's significantly less than the number of records created (about 50%, or 90% depending on which table).

So my question is, why is this query not deleting all the records that have been inserted into the new table?

Upvotes: 1

Views: 56

Answers (1)

moomin
moomin

Reputation: 59

Do those records have child records in other tables? If so, check whether "Cascade delete" is specified, as well as your privileges for those tables. Depending on the settings, sometimes you cannot delete records if that would leave orphans in other tables.

Upvotes: 1

Related Questions