Merge into with multiple updates and inserts Merge into with multiple updates and inserts sql sql

Merge into with multiple updates and inserts


Maybe I'm missing something but

The error message complains you can't have multiple WHEN MATCHED so you could convert

WHEN MATCHED AND TARGET.NodeId = Source.StartNodeId THEN    UPDATE SET TARGET.NodeId = SOURCE.StartNodeId,               TARGET.LinkId = SOURCE.IdWHEN MATCHED AND TARGET.NodeId = Source.EndNodeId THEN    UPDATE SET TARGET.NodeId = SOURCE.EndNodeId,               TARGET.LinkId = SOURCE.Id

to

WHEN MATCHED AND TARGET.NodeId IN (Source.StartNodeId,Source.EndNodeId) THEN    UPDATE SET TARGET.NodeId = CASE                                  WHEN TARGET.NodeId = Source.StartNodeId                                  THEN SOURCE.StartNodeId                                  ELSE Source.EndNodeId                                END,               TARGET.LinkId = SOURCE.Id

But as the first branch of the CASE is hit when TARGET.NodeId = Source.StartNodeId and also sets TARGET.NodeId = Source.StartNodeId and similarly for the second branch then that seems to simplify to

WHEN MATCHED AND TARGET.NodeId IN (Source.StartNodeId,Source.EndNodeId) THEN    UPDATE SET TARGET.LinkId = SOURCE.Id