Why WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement?
In your MERGE
statement, you have three WHEN MATCHED
clauses
- Two with an
UPDATE
statement - One with an
INSERT
statement.
However, that is not allowed. It is clearly stated in the Documentation on MERGE:
The
MERGE
statement can have at most twoWHEN MATCHED
clauses.
And
If there are two
WHEN MATCHED
clauses, then one must specify anUPDATE
action and one must specify aDELETE
action.
Also important to know is:
If
UPDATE
is specified in the <merge_matched> clause, and more than one row of <table_source> matches a row in target_table based on <merge_search_condition>, SQL Server returns an error. TheMERGE
statement cannot update the same row more than once, or update and delete the same row.