Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
This behaviour makes no sense to me. Why would rows that haven't been deleted appear in DELETED.*? –  thecoop Nov 5 '12 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. –  Martin Smith Nov 5 '12 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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