基本上我有一个 SQL Server 2008 R2 数据库。数据库中有一个名为 Node 和 Link 的表。Link 包含与 Node 中 Id 相关的 StartNodeId 和 EndNodeId。数据库还需要在 Node 和 Link 之间添加 Link 表,以便更快地检查诸如此节点是否与此链接相关或哪些节点与此链接相关等内容。Link 表包含标识键、NodeId 和 LinkId。我的问题是,在进行插入时,我尝试使用合并语句来完成我的操作,但似乎无法实现我想要的功能。
当我尝试时
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;
我收到了错误信息“MERGE语句的'UPDATE'子句中不能出现多个'type为WHEN MATCHED'的操作”。
如果尝试分别插入起始节点和终止节点,例如:
--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 MATCHED BY TARGET THEN
INSERT (LinkId, NodeId)
VALUES (SOURCE.Id, SOURCE.StartNodeId)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
--Insert End Node To Link Relationships
MERGE INTO [RoadRoutingDatabase].[dbo].[NodeToLink] AS TARGET
USING (SELECT Id, EndNodeId FROM [RoadRoutingDatabase].[dbo].[Link]) AS SOURCE
ON (TARGET.NodeId = SOURCE.EndNodeId AND TARGET.LinkId = SOURCE.Id)
WHEN MATCHED THEN
UPDATE SET TARGET.NodeId = SOURCE.EndNodeId,
TARGET.LinkId = SOURCE.Id
WHEN NOT MATCHED BY TARGET THEN
INSERT (LinkId, NodeId)
VALUES (SOURCE.Id, SOURCE.EndNodeId)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
链接被删除了(这不奇怪),所以我想知道有没有什么好方法可以解决这个问题?如果可能的话,我仍然希望能够使用合并语句来完成
谢谢
编辑:我找到了一种使用不同来源的不同方式来合并此数据的方法,问题现已解决。
TARGET.NodeId
呢?你似乎正在将其更新为与你尝试测试的完全相同的内容。 - Martin Smith