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
inCustomer_Transaction_Info
,Trns_Measure_Two
must be assigned to the correctBranch_Code
for that customer which is the one in the first table ,Customer_info
-
For each
Customer_num
inCustomer_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---> Update to 5
A 2 20 5---> Update to 0
A 3 14 0
-------------------------------------------------------------
B 2 10 10---> This record is Correct
B 1 36 0
-------------------------------------------------------------
C 3 14 0 ---> Update to 18
C 1 10 18---> Update to 0
We must take these facts into consideration that:
- There's only one
Customer_Branch
for eachCustomer_num
inCustomer_info
table. - There's only one non-zero
Trns_Measure_Two
for eachCustomer_num
inCustomer_Transaction_Info
Thanks in advance
Best Answer
fiddle