tsql - MERGE - 条件 "WHEN MATCHED"输出

Question

以下代码显示了两个 WHEN MATCHED 子句。当只有 DATETIME 列“updatedAt”发生变化时，第一个匹配。这应该会更新目标，但是我不想在 OUTPUT 中标记它。当给定匹配中有其他更改时，第二个匹配，这应该导致 OUTPUT。

MERGE [Target].dbo.[clients] AS target
USING [Source].dbo.[clients] AS source
ON target.[objectId]=source.[objectId]
WHEN MATCHED AND NOT EXISTS (
                    SELECT source.firstName, ...
                    EXCEPT
                    SELECT target.firstName, ...
                ) AND source.updatedAt <> target.updatedAt
THEN 
    UPDATE SET --THIS UPDATE SHOULD NOT LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.updatedAt = source.updatedAt
WHEN MATCHED AND EXISTS (
                    SELECT source.firstName, ... , source.updatedAt
                    EXCEPT
                    SELECT target.firstName, ... , target.updatedAt
                )
THEN
    UPDATE SET --THIS UPDATE SHOULD LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.[firstName]=source.[firstName], ...
WHEN NOT MATCHED BY TARGET
THEN
    INSERT ([objectId],[firstName], ... ,[updatedAt]) VALUES ([objectId],[firstName], ... ,[updatedAt])
WHEN NOT MATCHED BY SOURCE
THEN
    DELETE

OUTPUT 
    $ACTION ChangeType
    , ISNULL(
        inserted.objectId
        , deleted.objectId
    ) AS objectId
    , GETDATE() AS DateTimeChanged
;

这可以通过输出所有源列和目标列并将 MERGE 放在子查询中来实现，以便在主查询中进行比较，例如:

WHERE NOT (
    [ChangeType]='UPDATE' 
    AND [src objectId]=[tgt objectId] 
    AND [src firstName]=[tgt firstName] 
    AND ... 
    AND [src updatedAt]<>[tgt updatedAt]
)

但是，我觉得应该有更好的方法，因为我可以声明两个不同的 WHEN MATCHED 子句。有没有更好的办法？

Answer 1

我认为您的示例不正确，因为您有两个条件 WHEN MATCHED 子句。

根据在线图书:Merge (强调我的)

WHEN MATCHED THEN < merge_matched >
Specifies that all rows of *target_table, which match the rows returned by ON , and satisfy any additional search condition, are either updated or deleted according to the clause.

The MERGE statement can have, at most, two WHEN MATCHED clauses. If two clauses are specified, the first clause must be accompanied by an AND clause. For any given row, the second WHEN MATCHED clause is only applied if the first isn't. If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. When UPDATE is specified in the clause, and more than one row of matches a row in target_table based on , SQL Server returns an error. The MERGE statement can't update the same row more than once, or update and delete the same row.

我也试过你的代码

BEGIN TRANSACTION
SET XACT_ABORT ON;

CREATE TABLE TargetClients
(
    objectId BIGINT
    , firstName VARCHAR(50)
    , updatedAt DATETIME2(0)

)

CREATE TABLE SourceClients
(
    objectId BIGINT
    , firstName VARCHAR(50)
    , updatedAt DATETIME2(0)

)

go

MERGE TargetClients AS target
USING SourceClients AS source
ON target.[objectId]=source.[objectId]
WHEN MATCHED AND NOT EXISTS (
                    SELECT source.firstName
                    EXCEPT
                    SELECT target.firstName
                ) AND source.updatedAt <> target.updatedAt
THEN 
    UPDATE SET --THIS UPDATE SHOULD NOT LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.updatedAt = source.updatedAt
WHEN MATCHED AND EXISTS (
                    SELECT source.firstName, source.updatedAt
                    EXCEPT
                    SELECT target.firstName, target.updatedAt
                )
THEN
    UPDATE SET --THIS UPDATE SHOULD LEAD TO AN OUTPUT WITH $ACTION = 'UPDATE'
        target.[firstName]=source.[firstName]
WHEN NOT MATCHED BY TARGET
THEN
    INSERT ([objectId],[firstName], [updatedAt]) VALUES ([objectId],[firstName], [updatedAt])
WHEN NOT MATCHED BY SOURCE
THEN
    DELETE

OUTPUT 
    $ACTION ChangeType
    , ISNULL(
        inserted.objectId
        , deleted.objectId
    ) AS objectId
    , GETDATE() AS DateTimeChanged
;



rollback

它告诉我错误

Msg 10714, Level 15, State 1, Line 33 An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.

一个可能的解决方案是在 Merge_matched 子句中使用 CASE 语句，并使用一个额外的列作为标志。