Use Where Clause With Merge

Use Where Clause With Merge

 

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.

 

 

 

 

作者:Chuck Lu    GitHub