Sql-server – Use Where Clause With Merge

mergesql serversql-server-2008-r2t-sql

In the below syntax with the When Matched is it possible to only update if the value in the update table differs from the value in the employee table? Something similar to my below DDL – but of course this throws an error and does not work.

What should I update in my syntax to only update rows that are different? My desired update would be to not iterate each and every row that matches, but only update emp44 & emp55 since the address is different.

The error presented is

Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'WHERE'.

Syntax

  DECLARE @Emp Table (empid varchar(10), empaddress varchar(100))
  Insert Into @Emp Values 
  ('emp11', '111 No Blue'), 
  ('emp22', '222 No Blue'),
  ('emp33', '333 No Blue'), 
  ('emp44', '444 No Blue'),
  ('emp55', '555 No Blue');

Declare @EmpUpdates TABLE (empid varchar(10), empaddress varchar(100))
Insert Into @EmpUpdates Values
('emp11', '111 No Blue'), 
('emp22', '222 No Blue'),
('emp33', '333 No Blue'), 
('emp44', '999 No Blue'),
('emp55', '888 No Blue'),
('emp66', '4141 No Blue');

MERGE @Emp emp
Using @EmpUpdates eup
ON emp.empid = eup.empid
 WHEN MATCHED THEN
UPDATE
SET emp.empaddress = eup.empaddress
WHERE emp.empaddress <> eup.empaddress
WHEN NOT MATCHED BY TARGET THEN
INSERT (empid, empaddress)
VALUES(eup.empid, eup.empaddress)

Best Answer

There is no WHERE in that part of the MERGE statement. See MERGE (Transact-SQL) in the documentation for syntax help.

There is an optional AND part in WHEN MATCHED clause so the straightforward answer is to move the condition there:

MERGE @Emp emp
USING @EmpUpdates eup
ON emp.empid = eup.empid
WHEN MATCHED 
     AND emp.empaddress <> eup.empaddress
  THEN
    UPDATE
    SET emp.empaddress = eup.empaddress
WHEN NOT MATCHED BY TARGET 
  THEN 
    INSERT (empid, empaddress)
    VALUES (eup.empid, eup.empaddress) ;

Be careful to handle nulls correctly there (if empaddress is nullable).

It is sometimes more explicit (and efficient) to perform the operations in two separate statements (UPDATE and INSERT in your case) in a transaction.

Also, be aware there have been (and still are) some issues with MERGE.

Related Question