合并-只有当值改变时才更新

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.

现在，如果我只想更新行，并因此增加版本，只有当名称已经更改时。

2 个解决方案

#1

WHEN MATCHED can have AND . Also, no need to update EMP_ID .

当匹配时可以有和。而且，不需要更新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 NULL values while comparing trg.Last_Name <> src.Last_Name , for instance ISNULL(trg.Last_Name,'') <> ISNULL(src.Last_Name,'')

如果Last_Name或First_Name是nullable，则需要在比较trg时处理空值。Last_Name < > src。Last_Name，例如ISNULL(trg.Last_Name， ") <> ISNULL(src.Last_Name， ")

#2

Rather than avoiding an update altogether, you could change your [VERSION] + 1 code to add zero when names match:

您可以更改[VERSION] + 1代码，以便在名称匹配时添加0，而不是完全避免更新:

[VERSION] = tgt.VERSION + (CASE
    WHEN tgt.First_Name <> src.First_Name OR tgt.Last_Name <> src.Last_Name
    THEN 1
    ELSE 0 END)

#1