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