Update duplicate records from another table

Question

I have 2 table structure as follows,

TableA

TableB

I try to update TableB with the following query,

update tableB set ID = b.ID from tableB a inner join tableA b on a.prod = b.prod

Prod a in TableB is getting updated with only ID as 1, although Prod a also has IDs as 2 and 3 in TableA.

How could we modify the update query to get all the IDs in TableB?

Expected Output:

Answer 1

Try using MERGE concept,

MERGE TableB AS T
USING TableA AS S
ON (T.ID = S.ID and T.Prod = S.Prod) 
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(ID, Prod) VALUES(S.ID, S.Prod)
WHEN MATCHED 
    THEN UPDATE SET T.ID = S.ID, T.Prod = S.Prod

GO 

You should try this,

Update TableB set ID = A.ID from TableA A
Inner Join TableB B on B.Prod = A.Prod And B.ID = 0