时间戳

首页 > 解决方案 > 即使数据没有更新，MERGE 语句也会更新

sql-server - 即使数据没有更新，MERGE 语句也会更新

问题描述

我有一个合并语句，我想在其中更新我的表行，以防任何列具有不同的值。但似乎即使源表中的大多数行都保持不变，该MERGE语句执行 an UPDATEon 至少计算它所做的一个UPDATE.

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(50));

MERGE MyTarget AS TARGET
USING MySource AS SOURCE
ON (SOURCE.customeridHash = TARGET.Id)
WHEN MATCHED AND (TARGET.IsCompany <> SOURCE.company
    OR TARGET.Gender <> SOURCE.gender
    OR TARGET.BirthDate <> CONVERT(DATE, SOURCE.dateofbirth)
    OR TARGET.ZipCode <> SOURCE.ZipCode
    OR TARGET.City <> SOURCE.City
    OR TARGET.WantsEmail <> (CASE WHEN SOURCE.noemail = 0 THEN 1 ELSE 0 END)
    OR TARGET.WantsSMS <> (CASE WHEN SOURCE.nosms = 0 THEN 1 ELSE 0 END)
    OR TARGET.WantsDM <> (CASE WHEN SOURCE.nodirectmarketing = 0 THEN 1 ELSE 0 END)
    OR TARGET.WantsTM <> (CASE WHEN SOURCE.notelemarketing = 0 THEN 1 ELSE 0 END)
    OR TARGET.HasEmail <> SOURCE.HasEmail
    OR TARGET.HasMobilePhoneNumber <> SOURCE.HasMobilePhoneNumber
    OR TARGET.HasPhoneNumber = SOURCE.HasPhoneNumber
    OR TARGET.Created <> SOURCE.Created
    OR TARGET.Updated <> SOURCE.changed)
THEN
UPDATE SET TARGET.IsCompany = SOURCE.company,
    TARGET.Gender = SOURCE.gender,
    TARGET.BirthDate = CONVERT(DATE, SOURCE.dateofbirth),
    TARGET.ZipCode = SOURCE.ZipCode,
    TARGET.City = SOURCE.City,
    TARGET.WantsEmail = (CASE WHEN SOURCE.noemail = 0 THEN 1 ELSE 0 END),
    TARGET.WantsSMS = (CASE WHEN SOURCE.nosms = 0 THEN 1 ELSE 0 END),
    TARGET.WantsDM = (CASE WHEN SOURCE.nodirectmarketing = 0 THEN 1 ELSE 0 END),
    TARGET.WantsTM = (CASE WHEN SOURCE.notelemarketing = 0 THEN 1 ELSE 0 END),
    TARGET.HasEmail = SOURCE.HasEmail,
    TARGET.HasMobilePhoneNumber = SOURCE.HasMobilePhoneNumber,
    TARGET.HasPhoneNumber = SOURCE.HasPhoneNumber,
    TARGET.Created = SOURCE.Created,
    TARGET.Updated = SOURCE.changed
WHEN NOT MATCHED BY TARGET THEN
INSERT (
    Id, 
    IsCompany, 
    Gender, 
    BirthDate, 
    ZipCode, 
    City, 
    WantsEmail, 
    WantsSMS,
    WantsDM, 
    WantsTM, 
    HasEmail, 
    HasMobilePhoneNumber, 
    HasPhoneNumber, 
    Created, 
    Updated
)
VALUES (
    SOURCE.customeridHash,
    SOURCE.company,
    SOURCE.gender,
    CONVERT(DATE, SOURCE.dateofbirth),
    SOURCE.ZipCode,
    SOURCE.City,
    (CASE WHEN SOURCE.noemail = 0 THEN 1 ELSE 0 END),
    (CASE WHEN SOURCE.nosms = 0 THEN 1 ELSE 0 END),
    (CASE WHEN SOURCE.nodirectmarketing = 0 THEN 1 ELSE 0 END),
    (CASE WHEN SOURCE.notelemarketing = 0 THEN 1 ELSE 0 END),
    SOURCE.HasEmail,
    SOURCE.HasMobilePhoneNumber,
    SOURCE.HasPhoneNumber,
    SOURCE.Created,
    SOURCE.changed
)
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $action INTO @SummaryOfChanges;

SELECT Change, COUNT(*) CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

我在更新结束时（最后SELECT）做了一些簿记，似乎几乎所有不是新的行都被更新了。<>这是一种常见的行为，还是在我的比较中真的有WHEN MATCHED AND...更新的价值？

更新：根据其中一条评论的建议，我编写了以下测试来检查我的条件是否触发更新：

-- TEST MERGE
select count(*) 
from MyTarget TARGET join MySource SOURCE on TARGET.Id=SOURCE.customeridHash
where TARGET.IsCompany <> SOURCE.company
    OR TARGET.Gender <> SOURCE.gender
    OR TARGET.BirthDate <> CONVERT(DATE, SOURCE.dateofbirth)
    OR TARGET.ZipCode <> SOURCE.ZipCode
    OR TARGET.City <> SOURCE.City
    OR TARGET.WantsEmail <> (CASE WHEN SOURCE.noemail = 0 THEN 1 ELSE 0 END)
    OR TARGET.WantsSMS <> (CASE WHEN SOURCE.nosms = 0 THEN 1 ELSE 0 END)
    OR TARGET.WantsDM <> (CASE WHEN SOURCE.nodirectmarketing = 0 THEN 1 ELSE 0 END)
    OR TARGET.WantsTM <> (CASE WHEN SOURCE.notelemarketing = 0 THEN 1 ELSE 0 END)
    OR TARGET.HasEmail <> SOURCE.HasEmail
    OR TARGET.HasMobilePhoneNumber <> SOURCE.HasMobilePhoneNumber
    OR TARGET.HasPhoneNumber = SOURCE.HasPhoneNumber
    OR TARGET.Created <> SOURCE.Created
    OR TARGET.Updated <> SOURCE.changed;

我意识到这个查询返回相同数量的更新。所以它在某种程度上更多地是关于MERGE语句的条件。但我想知道它们是如何触发更新的。

标签： sql-servertsqlmerge

解决方案

我想我发现了自己的错误，在我写的条件下：

OR TARGET.HasPhoneNumber = SOURCE.HasPhoneNumber

这几乎总是真实的！

推荐阅读