8

I'm adding a foreign key to a table, and removing any rows that violate the FK, copying them into a ModifiedTable_invalid table. As part of the script, I've got the following MERGE command:

MERGE ModifiedTable t1
USING TargetTable tt
ON t1.JoinColumn = tt.JoinColumn
WHEN MATCHED THEN
    UPDATE SET t1.FkColumn = tt.FkSource
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT DELETED.* INTO ModifiedTable_invalid;

However, this command seems to be inserting EVERY row from ModifiedTable into ModifiedTable_invalid, not just those deleted by the MERGE command. What's going on, and how do I get it to only put the deleted rows in ModifiedTable_invalid?

1 Answer 1

11

When you update a row, it will appear in both the inserted (post-update value) and deleted (pre-update value) pseudo-tables. If this seems odd, consider that an update is logically a deletion followed by an insert (though the update may not be physically performed that way).

When used with MERGE, the OUTPUT clause can include an extra column named $action. Adding this column to your query will show which action was taken ('INSERT', 'UPDATE', or 'DELETE') for each row.

For example:

insert into ModifiedTable_invalid(Id /* And other columns */)
select
    Id
    /* And other columns */
from
(
    merge ModifiedTable t1
    using TargetTable t2 on t1.JoinColumn = t2.JoinColumn
    when matched then update set t1.FkColumn = t2.FkSource
    when not matched by source then delete
    output 
        $action as DMLAction,
        deleted.Id as Id /* And other columns... */
) outputs
where
    DMLAction = 'DELETE';

Updated rows will have $action = 'UPDATE'.

Also see Adam Machanic's post on using OUTPUT with the MERGE statement for some other nice examples.

2
  • This behaviour makes no sense to me. Why would rows that haven't been deleted appear in DELETED.*?
    – thecoop
    Nov 5, 2012 at 17:22
  • 3
    @thecoop - It allows you to access the "before" and "after" values for an update. Conceptually you might consider an update to be a delete followed by an insert even though that often isn't how it actually happens. Nov 5, 2012 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.