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