sql - 合并语句中未更新的行

Question

当唯一标识符再次出现在临时表中时，我有一个合并语句来更新输出表中的值。但唯一值再次出现，但作为新记录插入，而不是更新现有记录。

以下是暂存表中的记录（我不包括其他字段，因为这些字段不相关并且不想让示例变得更加困难）：

InsertDate  App_id  DeviceName  Account Name
31/07/2019  APP-354 SCRBSCVDK002279 #avahi-autoipd

以下是输出表中的现有记录：

ID  InsertDate  App_id  DeviceName  Account Name
139059  29/04/2019  APP-354 SCRBSCVDK002279 #avahi-autoipd

合并是使用名为 Unique_Comborow 的列进行的，它是 3 个字段的串联：

App_id  DeviceName  Account Name

我已经检查了语法并成功测试了它。

MERGE Output_Linux_Accounts AS O
USING (SELECT * FROM [dbo].[StagingLinuxDiscoveryData] 
WHERE 1=1
AND [Rank] = 1) AS S
ON  S.Unique_Comborow = O.Unique_Comborow COLLATE SQL_Latin1_General_CP1_CS_AS
WHEN MATCHED THEN
    UPDATE SET 
        O.DerivedAccountCategory                    = S.DerivedAccountCategory,
        --O.adm_status                              = S.adm_status,
        O.user_status                               = S.user_status,
        --O.pam_remediation                         = S.pam_remediation,
        O.[Local Mapped Account]                    = S.[Local Mapped Account],
        O.[Account Display Name]                    = S.[Account Display Name],
        O.[Account Type]                            = S.[Account Type],
        O.[Account Category]                        = S.[Account Category],
        O.[Account Group]                           = S.[Account Group],
        O.[Account Description]                     = S.[Account Description],
        O.[Compliance Status]                       = S.[Compliance Status],
        O.[Account State]                           = S.[Account State],
        O.[Password Never Expires]                  = S.[Password Never Expires],
        O.[Password Age]                            = S.[Password Age],
        O.[Password Last Set]                       = S.[Password Last Set],
        O.[Insecure Privilege Escalation]           = S.[Insecure Privilege Escalation],
        O.[Insecure Privilege Escalation Reason]    = S.[Insecure Privilege Escalation Reason],
        O.[Last Login Date]                         = S.[Last Login Date],
        O.[Account Expiration Date]                 = S.[Account Expiration Date],
        O.[OS Version]                              = S.[OS Version],
        O.Details                                   = S.Details,
        O.Notes                                     =  Concat('Updated on - ', S.InsertDate),
        O.UpdateDate                                = S.InsertDate,
        O.[LogonAge]                                = S.[LogonAge],
        O.[Number of Keys Found]                    = S.[Number of Keys Found],
        O.[Last Key Update Date]                    = S.[Last Key Update Date],
        O.[KeyAge]                                  = S.[KeyAge],
        O.[Key Length]                              = S.[Key Length],
        O.[Key Algorithm]                           = S.[Key Algorithm],
        O.[SSH Server]                              = S.[SSH Server],
        O.[Key Comment]                             = S.[Key Comment],
        O.[Command Run on Login]                    = S.[Command Run on Login],
        O.[Key Fingerprint]                         = S.[Key Fingerprint],
        O.[In_latest_Scan]                          = S.[In_latest_Scan]
WHEN NOT MATCHED BY TARGET THEN --****not including the rest of the insert statement as this is not relevant*****
    INSERT (....)
VALUES

以下是预期结果（我不包括其他字段，因为这些不相关并且不想使示例更困难）：

ID  InsertDate  App_id  DeviceName  Account Name
139059  31/07/2019  APP-354 SCRBSCVDK002279 #avahi-autoipd

以下是应用数据的实际结果

ID  InsertDate  App_id  DeviceName  Account Name
434553  31/07/2019  APP-354 SCRBSCVDK002279 #avahi-autoipd
139059  29/04/2019  APP-354 SCRBSCVDK002279 #avahi-autoipd

标签： sqlsql-servermerge