I have two tables Product_Staging and Product. The contents of the table are below.
Product_Staging:
Account_No Product_No Cur_Revenue Prev_Revenue
12 AB 5.0 3.0
13 BC 4.0 4.0
15 DF 10.0 7.5
Product:
Account_No Product_No Cur_Revenue Prev_Revenue
12 AB 1.0 3.0
13 BC 4.0 5.0
16 DF 10.0 17.5
17 CG 5.0 6.0
I need to update the Product table's Cur_Revenue and Prev_Revenue fields with the contents from Product_Staging table when Account_No and Product_No matched, otherwise clear the values in Product table. Sample output is below.
Product After Update:
Account_No Product_No Cur_Revenue Prev_Revenue
12 AB 5.0 3.0 (Updated from Product_Staging)
13 BC 4.0 4.0 (Updated from Product_Staging)
16 DF (Cleared)
17 CG (Cleared)
Row 15/DF from Product_Staging will be discarded as it does not exist in the Product table.
You can try to use update .... OUTER JOIN
update p SET
p.Cur_Revenue = ps.Cur_Revenue,
p.Prev_Revenue = ps.Prev_Revenue
from Product p
LEFT JOIN Product_Staging ps on
p.Account_No= ps.Account_No
If you want to set a default value but the row does not exist in the Product table, you can try to use ISNULL function.
update p SET
p.Cur_Revenue = ISNULL(ps.Cur_Revenue,0),
p.Prev_Revenue = ISNULL(ps.Prev_Revenue,0)
from Product p
LEFT JOIN Product_Staging ps on
p.Account_No= ps.Account_No