Query to update one column of a table based on a column of a different table I have two tables with below structure :
Create table Customer_info
(Customer_num  varchar(50),
 Customer_Branch  int);

Create table Customer_Transaction_Info
(Customer_num     varchar(50),
 Branch_Code      int,
 Trns_Measure_One int,
 Trns_Measure_Two int
 );

Sample data for each table:
insert into Customer_info(Customer_num , Customer_Branch)
values('A',1),
      ('B',2),
      ('C',3);

   Customer_num     Customer_Branch
------------------- ---------------
     A                      1
     B                      2
     C                      3

insert into Customer_Transaction_Info (Customer_num,Branch_Code,Trns_Measure_One,Trns_Measure_Two)
Values ('A',1,10,0),
       ('A',2,20,5),
       ('A',3,14,0),
       ('B',2,10,10),
       ('B',1,36,0),
       ('C',3,14,0),
       ('C',1,10,18);

 Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0
    A               2           20               5
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0
    C               1           10               18

What I need to do is this:

For each Customer_num in Customer_Transaction_Info , Trns_Measure_Two must be assigned to the correct Branch_Code for that customer which is the one in the first table , Customer_info

For each Customer_num in Customer_Transaction_Info, Trns_Measure_Two must be updated to 0 for  incorrect branches.


So based on these the desired result is below:
 Customer_num    Branch_Code Trns_Measure_One Trns_Measure_Two
 --------------- ----------- ----------------  ---------------
    A               1           10               0---&gt; Update to 5
    A               2           20               5---&gt; Update to 0
    A               3           14               0
 -------------------------------------------------------------
    B               2           10               10---&gt; This record is Correct
    B               1           36               0
 -------------------------------------------------------------
    C               3           14               0 ---&gt; Update to 18
    C               1           10               18---&gt; Update to 0

We must take these facts into consideration that:

There's only one Customer_Branch for each Customer_num in Customer_info table.
There's only one non-zero Trns_Measure_Two for each Customer_num in Customer_Transaction_Info

Thanks in advance
This should solve the first requirement:
UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = CTI2.Trns_Measure_Two -- Set it to the correct value (the only non-zero value for that Customer_num)
FROM Customer_Transaction_Info AS CTI
INNER JOIN Customer_info AS CI -- Inner joining Customer_info to ensure we only update for the correct Customer and Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
INNER JOIN Customer_Transaction_Info AS CTI2 -- Need to re-join to Customer_Transaction_Info on Customer_num and Trns_Measure_Two &lt;&gt; 0 to get this customer's non-zero correct value
    ON CTI.Customer_num = CTI2.Customer_num
    AND CTI2.Trns_Measure_Two &lt;&gt; 0

This should update the incorrect branches for your second requirement:
UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = 0 -- Setting incorrect values to 0
FROM Customer_Transaction_Info AS CTI
LEFT JOIN Customer_info AS CI -- Left joining to Customer_info so we can see where there's no match by Customer_num and Customer_Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
WHERE CI.Customer_num IS NULL -- This filters Customer_Transaction_Info to only the bad records (where there's no match to Customer_info)

Please see the comments for information on how these two update statements work.