在 WHEN MATCHED 中使用多个语句 [英] Using multiple statements in WHEN MATCHED

问题描述

我使用 MERGE 语句将 XML 输入插入到 SQL Server 数据库表中.如何在 WHEN MATCHED 块中执行多个条件.请参考以下代码.

I am using MERGE statement in order to insert XML input to SQL Server database table. How to execute multiple conditions in WHEN MATCHED block. Please refer the below code.

USING TableRelationship AS new
  ON (new.TableRelationshipTypeID = old.TableRelationshipTypeID) AND old.ToRoleID = @RoleID

WHEN MATCHED THEN 
   UPDATE 
       SET old.FromRoleID = new.FromRoleID
   -- Condition 2
   -- Condition 3

当前 WHEN MATCHED 它只执行这个 old.FromRoleID = new.FromRoleID 行.如何在 WHEN NOT MATCHED 条件内执行所有 3 行(-- Condition 2 and 3).

Currently WHEN MATCHED it only executes this old.FromRoleID = new.FromRoleID line. How can I execute all 3 lines (-- Condition 2 and 3) inside WHEN NOT MATCHED condition.

例如:

这正是我所期望的.WHEN MATCHED 我只想更新旧字段(old.ThruDate = GETDATE())并将记录插入到同一个表中.我不能用逗号分隔这些语句.SQL 发出

This is what I expect. WHEN MATCHED I just want to update the old field (old.ThruDate = GETDATE()) and insert a record to the same table. I cant separate those statements by a comma. SQL emits

不正确的语法

MERGE INTO Party.TableRelationship AS old
USING TableRelationship AS new ON (new.TableRelationshipTypeID = old.TableRelationshipTypeID) AND old.ToRoleID = @RoleID

WHEN MATCHED THEN 
   UPDATE 
       SET old.ThruDate = GETDATE(),
   INSERT (FromRoleID, ToRoleID, TableRelationshipTypeID)
   VALUES (new.FromRoleID, new.ToRoleID, new.TableRelationshipTypeID);

谢谢.

推荐答案

你可以使用 INSERT over DML 来实现:

You could use INSERT over DML to achieve it:

INSERT INTO tab_name(FromRoleID, ToRoleID, TableRelationshipTypeID)
SELECT FromRoleID, ToRoleID, TableRelationshipTypeID
FROM (
  MERGE INTO Party.TableRelationship AS old
  USING TableRelationship AS new 
     ON new.TableRelationshipTypeID = old.TableRelationshipTypeID 
    AND old.ToRoleID = @RoleID
  WHEN MATCHED THEN 
    UPDATE SET old.ThruDate = GETDATE()
    OUTPUT $action, FromRoleID, ToRoleID, TableRelationshipTypeID
) sub(action, FromRoleID, ToRoleID, TableRelationshipTypeID)
WHERE action = 'UPDATE';

请记住，此方法有一些限制更多信息:MS 连接

Keep in mind that this method has some limitations more info: MS Connect

这篇关于在 WHEN MATCHED 中使用多个语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！