SQL 合并 - 仅在值更改时更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16107273/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merge - Only update if values have changed
提问by TrialAndError
I am running a merge in SQL Server. In my update, I want to only update the row if the values have changed. There is a version row that increments on each update. Below is an example:
我正在 SQL Server 中运行合并。在我的更新中，如果值已更改，我只想更新该行。每次更新都会增加一个版本行。下面是一个例子：
MERGE Employee as tgt USING
(SELECT Employee_History.Emp_ID
, Employee_History.First_Name
, Employee_History.Last_Name
FROM Employee_History)
as src (Emp_ID,First_Name,Last_Name)
ON tgt.Emp_ID = src.Emp_ID
WHEN MATCHED THEN
UPDATE SET
Emp_ID = src.Emp_ID,
,[VERSION] = tgt.VERSION + 1
,First_Name = src.First_Name
,Last_Name = src.Last_Name
WHEN NOT MATCHED BY target THEN
INSERT (Emp_ID,0,First_Name,Last_Name)
VALUES
(src.Emp_ID,[VERSION],src.First_Name,src.Last_Name);
Now, if I only wanted to update the row, and thus increment version, ONLY if the name has changed.
现在，如果我只想更新行，从而增加版本，仅当名称已更改。
回答by a1ex07
WHEN MATCHEDcan have AND. Also, no need to update EMP_ID.
WHEN MATCHED可以有AND。此外，无需更新EMP_ID.
...
WHEN MATCHED AND (trg.First_Name <> src.First_Name
OR trg.Last_Name <> src.Last_Name) THEN UPDATE
SET
[VERSION] = tgt.VERSION + 1
,First_Name = src.First_Name
,Last_Name = src.Last_Name
...
If Last_Name or First_Name are nullable, you need to take care of NULLvalues while comparing trg.Last_Name <> src.Last_Name , for instance ISNULL(trg.Last_Name,'') <> ISNULL(src.Last_Name,'')
如果 Last_Name 或 First_Name 可以为空，NULL则在比较 trg.Last_Name <> src.Last_Name 时需要注意值，例如ISNULL(trg.Last_Name,'') <> ISNULL(src.Last_Name,'')
回答by dasblinkenlight
Rather than avoiding an update altogether, you could change your [VERSION] + 1code to add zero when names match:
您可以更改[VERSION] + 1代码以在名称匹配时添加零，而不是完全避免更新：
[VERSION] = tgt.VERSION + (CASE
WHEN tgt.First_Name <> src.First_Name OR tgt.Last_Name <> src.Last_Name
THEN 1
ELSE 0 END)
回答by b_levitt
The answer provided by a1ex07 is the right answer, but i just wanted to expand on the difficulty in comparing a large number of columns, watching for nulls, etc.
a1ex07 提供的答案是正确的答案，但我只是想扩展比较大量列、观察空值等的难度。
I found that I could generate a checksum in some CTE's with hashbytes, target those CTEs in the merge, and then use the "update and...." condition specified above to compare the hashes:
我发现我可以在一些带有哈希字节的 CTE 中生成校验和，在合并中定位这些 CTE，然后使用上面指定的“更新和....”条件来比较哈希：
with SourcePermissions as (
SELECT 1 as Code, 1013 as ObjectTypeCode, 'Create Market' as ActionName, null as ModuleCode, 1 as AssignableTargetFlags
union all SELECT 2, 1013, 'View Market', null, 1
union all SELECT 3, 1013, 'Edit Market', null, 1
--...shortened....
)
,SourcePermissions2 as (
select sp.*, HASHBYTES('sha2_256', xmlcol) as [Checksum]
from SourcePermissions sp
cross apply (select sp.* for xml raw) x(xmlcol)
)
,TargetPermissions as (
select p.*, HASHBYTES('sha2_256', xmlcol) as [Checksum]
from Permission p
cross apply (select p.* for xml raw) x(xmlcol)
) --select * from SourcePermissions2 sp join TargetPermissions tp on sp.code=tp.code where sp.Checksum = tp.Checksum
MERGE TargetPermissions AS target
USING (select * from SourcePermissions2) AS source ([Code] , [ObjectTypeCode] , [ActionName] , [ModuleCode] , [AssignableTargetFlags], [Checksum])
ON (target.Code = source.Code)
WHEN MATCHED and source.[Checksum] != target.[Checksum] then
UPDATE SET [ObjectTypeCode] = source.[ObjectTypeCode], [ActionName]=source.[ActionName], [ModuleCode]=source.[ModuleCode], [AssignableTargetFlags] = source.[AssignableTargetFlags]
WHEN NOT MATCHED THEN
INSERT ([Code] , [ObjectTypeCode] , [ActionName] , [ModuleCode] , [AssignableTargetFlags])
VALUES (source.[Code] , source.[ObjectTypeCode] , source.[ActionName] , source.[ModuleCode] , source.[AssignableTargetFlags])
OUTPUT deleted.*, $action, inserted.[Code]
--only minor issue is that you can no longer do a inserted.* here since it gives error 404 (sql, not web), complaining about returning checksum which is included in the target cte but not the underlying table
,inserted.[ObjectTypeCode] , inserted.[ActionName] , inserted.[ModuleCode] , inserted.[AssignableTargetFlags]
;
Couple of notes: I could have simplified greatly with checksum or binary_checksum, but I always get collisions with those.
几个注意事项：我可以用校验和或 binary_checksum 大大简化，但我总是与它们发生冲突。
As to the 'why', this is part of an automated deployment to keep a lookup table up to date. The problem with the merge though is there is an indexed view that is complex and heavily used, so updates to the related tables are quite expensive.
至于“为什么”，这是使查找表保持最新的自动化部署的一部分。合并的问题是有一个复杂且大量使用的索引视图，因此对相关表的更新非常昂贵。
回答by jayesh
@a1ex07 thanks for the answer.. a slight correction.. I am not following SQL version so this could be a change in SQL specification
@a1ex07 感谢您的回答.. 稍微更正.. 我没有关注 SQL 版本，所以这可能是 SQL 规范的变化
WHEN MATCHED AND CONDITIONTHEN UPDATE
当匹配和条件然后更新
The above is not a valid syntax
以上不是有效的语法
Following is valid
以下有效
WHEN MATCHED THEN UPDATE SET ... WHERE CONDITIONWHEN NOT MATCHED THEN INSERT...
匹配时，则更新设置...当条件不匹配时，则插入...
so would change it to
所以会把它改成
WHEN MATCHED THEN UPDATE
SET
[VERSION] = tgt.VERSION + 1
,First_Name = src.First_Name
,Last_Name = src.Last_Name
WHERE trg.First_Name <> src.First_Name OR trg.Last_Name <> src.Last_Name
WHERE trg.First_Name <> src.First_Name OR trg.Last_Name <> src.Last_Name
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
