MS SQL Server Update or Clear based on matching values

I need some help in a scenario that I am working on.

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.

Can someone help?

Answer

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

sqlfiddle

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