Edit Question - KnowledgeHub

Title Body

Basically I have a SQL Server 2008 R2 database. The database has a table called Node and Link. Link contains a StartNodeId and EndNodeId relating to a Id in Node. The database also requires a Link table between Node and Link for quicker checking of say, is this Node related to this Link or which Nodes are related to this Link. The Link table contains a Identity key, NodeId and LinkId. My problem is when I am doing my inserts I am trying to use merge statements which do not seem to be able to do what I am trying When I tried MERGE INTO [RoadRoutingDatabase].[dbo].[NodeToLink] AS TARGET USING (SELECT Id, StartNodeId, EndNodeId FROM [RoadRoutingDatabase].[dbo].[Link]) AS SOURCE ON (TARGET.LinkId = SOURCE.Id) WHEN MATCHED AND TARGET.NodeId = Source.StartNodeId THEN UPDATE SET TARGET.NodeId = SOURCE.StartNodeId, TARGET.LinkId = SOURCE.Id WHEN MATCHED AND TARGET.NodeId = Source.EndNodeId THEN UPDATE SET TARGET.NodeId = SOURCE.EndNodeId, TARGET.LinkId = SOURCE.Id WHEN NOT MATCHED BY TARGET AND TARGET.NodeId = Source.StartNodeId THEN INSERT (LinkId, NodeId) VALUES (SOURCE.Id, SOURCE.StartNodeId) WHEN NOT MATCHED BY TARGET AND TARGET.NodeId = Source.EndNodeId THEN INSERT (LinkId, NodeId) VALUES (SOURCE.Id, SOURCE.EndNodeId) WHEN NOT MATCHED BY SOURCE THEN DELETE; I get the error message "An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement" If I try inserting Start Nodes and End Nodes seperatly e.g. --Insert Start Node To Link Relationships MERGE INTO [RoadRoutingDatabase].[dbo].[NodeToLink] AS TARGET USING (SELECT Id, StartNodeId FROM [RoadRoutingDatabase].[dbo].[Link]) AS SOURCE ON (TARGET.NodeId = SOURCE.StartNodeId AND TARGET.LinkId = SOURCE.Id) WHEN MATCHED THEN UPDATE SET TARGET.NodeId = SOURCE.StartNodeId, TARGET.LinkId = SOURCE.Id WHEN NOT MA

Tags (comma-separated) Cancel