合并多个更新和插入到一个表中的命令

Question

基本上我有一个 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;

链接被删除了（这不奇怪），所以我想知道有没有什么好方法可以解决这个问题？如果可能的话，我仍然希望能够使用合并语句来完成

谢谢

编辑：我找到了一种使用不同来源的不同方式来合并此数据的方法，问题现已解决。

- Manatherin

---

你为什么要更新 TARGET.NodeId 呢？你似乎正在将其更新为与你尝试测试的完全相同的内容。 - Martin Smith

Answer 1

也许我漏掉了什么，但

错误信息提示你不能有多个 WHEN MATCHED，所以你可以将其转换为

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 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

但是，当 TARGET.NodeId = Source.StartNodeId 时，第一个分支被触发并设置了 TARGET.NodeId = Source.StartNodeId ，第二个分支同样如此。因此，看起来可以简化为

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

- Martin Smith