T-SQL MERGE (Ver2)

A reminder,

Merge ...
WHEN Matched And (Search condition) THEN
Update Set ****

Update can only be use once in Merge statement, so if there're multiple conditions, you will need to specify all of them in (Search Condition).

WHEN Not Matched cannot use Update, Only Delete/Insert can be used.

Example:
To update Product RRP with source table, conditions: update using source if source RRP less than current RRP, or if current RRP is 0 but source isn't.


Merge dbo.Products ip
USING (Select vendor_code, min(RRP) as Rp FROM zab_Supplierpricelist Where RRP > 0 and vendor_code > ''
Group by  vendor_code) as ms
ON ip.zextpartno = ms.vendor_code AND ip.status in ('A','R') WHEN Matched and ((ip.ZRRP > ms.Rp) OR (ip.ZRRP =0 AND ms.RP > 0)) THEN
UPDATE SET ip.ZRRP = ms.RP;