查詢以根據不同表的列更新表的一列

我有兩個具有以下結構的表：

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
);

每個表的樣本數據：

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

我需要做的是：

1. 對於每個Customer_numin Customer_Transaction_Info，Trns_Measure_Two必須為Branch_Code第一個表中的那個客戶分配正確的，Customer_info
2. 對於每個Customer_numin Customer_Transaction_Info，Trns_Measure_Two對於不正確的分支，必須更新為 0。

因此，基於這些，所需的結果如下：

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

我們必須考慮到這些事實：

1. 表中Customer_Branch每個只有一個。Customer_num``Customer_info
2. Trns_Measure_Two每一個Customer_num只有一個非零Customer_Transaction_Info

提前致謝

UPDATE Customer_Transaction_Info
SET Trns_Measure_Two = subquery.Trns_Measure_Two
FROM (SELECT cti.Customer_num, cti.Branch_Code, cti.Trns_Measure_One,
            CASE WHEN ci.Customer_Branch = cti.Branch_Code
                 THEN MAX(cti.Trns_Measure_Two) OVER (PARTITION BY cti.Customer_num)
                 ELSE 0
                 END Trns_Measure_Two
     FROM Customer_info ci
     JOIN Customer_Transaction_Info cti ON ci.Customer_num = cti.Customer_num) subquery
WHERE Customer_Transaction_Info.Customer_num = subquery.Customer_num
 AND Customer_Transaction_Info.Branch_Code = subquery.Branch_Code

小提琴

引用自：https://dba.stackexchange.com/questions/280581