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 two WHEN MATCHED clauses.

And

If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE 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. The MERGE statement cannot update the same row more than once, or update and delete the same row.