SQL 合并多个更新和插入

Merge into with multiple updates and inserts

提问by Manatherin

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

基本上我有一个 SQL Server 2008 R2 数据库。该数据库有一个名为 Node 和 Link 的表。链接包含与节点中的 Id 相关的 StartNodeId 和 EndNodeId。数据库还需要节点和链接之间的链接表，以便更快地检查这个节点是否与此链接相关或哪些节点与此链接相关。链接表包含身份密钥、NodeId 和 LinkId。我的问题是当我做我的插入时，我试图使用似乎无法完成我正在尝试的合并语句

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"

我收到错误消息“在 MERGE 语句的‘更新’子句中，‘WHEN MATCHED’类型的操作不能出现多次”

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

I end up with links being deleted (not surprising) so basically I was wondering if anyone knew of a good way of doing this? If possible I would like to be able to do it still using a merge statement

我最终删除了链接（并不奇怪）所以基本上我想知道是否有人知道这样做的好方法？如果可能的话，我希望能够仍然使用合并语句

Thanks

谢谢

Edit:I have found a different way of merging this data using a different source, the problem is now solved.

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

回答by Martin Smith

Maybe I'm missing something but

也许我错过了一些东西但是

The error message complains you can't have multiple WHEN MATCHEDso you could convert

错误消息抱怨你不能有多个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

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 CASEis hit when TARGET.NodeId = Source.StartNodeIdand also sets TARGET.NodeId = Source.StartNodeIdand similarly for the second branch then that seems to simplify to

但是当第一个分支CASE被击中时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