时间戳

首页 > 解决方案 > 使用同一表中的另一组记录更新表中某些行的列

sql - 使用同一表中的另一组记录更新表中某些行的列

问题描述

我有一张表（银行），上面有这样的记录——

BRANCH    | CITY        |  STATE     |  ZIP
-----------------------------------------------
Corporate |  City1      |  State1    |  Zip1
Corporate |  City2      |  State2    |  Zip2
Corporate |  City3      |  State3    |  Zip3
Corporate |  City4      |  State4    |  Zip4
Corporate |  City5      |  State5    |  Zip5
Corporate |  City6      |  State6    |  Zip6
1         |  City1      |  State1    |  
1         |  City4      |  State4    |  Zip4
1         |  City6      |  State6    |  Zip2
1         |  City10     |  State10   |  Zip10
2         |  City1      |  State1    |  Zip1
2         |  City2      |  State2    |  Zip2
2         |  City3      |  State3    |  Zip3
2         |  City5      |  State5    |  Zip4

需要通过匹配 'City' 和 'State' 使用 Branch 'Corporate' 更新 Branch '1' 的 ZIP 列。它不应更新分支 1 的记录 City10，即 Zip 应保持 Zip10。最后结果 - 

BRANCH    | CITY        |  STATE     |  ZIP
-----------------------------------------------
Corporate |  City1      |  State1    |  Zip1
Corporate |  City2      |  State2    |  Zip2
Corporate |  City3      |  State3    |  Zip3
Corporate |  City4      |  State4    |  Zip4
Corporate |  City5      |  State5    |  Zip5
Corporate |  City6      |  State6    |  Zip6
1         |  City1      |  State1    |  Zip1
1         |  City4      |  State4    |  Zip4
1         |  City6      |  State6    |  Zip6
1         |  City10     |  State10   |  Zip10
2         |  City1      |  State1    |  Zip1
2         |  City2      |  State2    |  Zip2
2         |  City3      |  State3    |  Zip3
2         |  City5      |  State5    |  Zip4

我使用了以下 SQL，但它正在更新 Branch 1 的记录，其中未找到 Corporate 的 City 和 State 匹配项。

Update Bank T1 
    set T1.zip = (select max(T2.zip) from Bank T2 where T1.city = T2.city and T1.state = T2.state and T2.branch ='Corporate' )
    where T1.branch = '1'

标签： sqloracle-sqldeveloper

解决方案

您可以使用显式join：

update Bank b
    set b.zip = b2.zip
    from Bank b join
         (select city, state, max(b2.zip) as zip
          from Bank b2
          where b2.branch = 'Corporate'
          group by city, state
         ) b2
         on b2.city = b.city and b2.state = b.state and 
    where b.branch = '1';

您也可以使用您的方法，但您需要考虑不匹配的值：

update Bank 
    set b.zip = (select coalesce(max(b2.zip), bank.zip)
                 from Bank b2
                 where b2.city = bank.city and b2.state = bank.state and b2.branch = 'Corporate'
                )
    where branch = '1';

但是，这将不必要地更新值不变的行。

推荐阅读