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:
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.
WHEN MATCHED
can have AND
. Also, no need to update 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,'')