如何用不同的值更新两个相同的行

Question

CREATE TABLE T1
(
ACCT_GRP_CD NUMBER(9),
MAT_CO_CD VARCHAR2(20),
ACCT_GRP_NM VARCHAR2(20),
ACCT_GRP_DESC VARCHAR2(20)
)
INSERT INTO T1 VALUES (829, 'MUSA', 'Z HEB', 'Z HEB')
INSERT INTO T1 VALUES (829, 'MUSA', 'Z HEB', 'Z HEB')
SELECT *
FROM T1

返回以下输出:

ACCT_GRP_CD  MAT_CO_CD  ACCT_GRP_NM   ACCT_GRP_DESC
---------------------------------------------------
829           MUSA        Z HEB          Z HEB
829           MUSA        Z HEB          Z HEB

预期结果:

ACCT_GRP_CD  MAT_CO_CD  ACCT_GRP_NM   ACCT_GRP_DESC
---------------------------------------------------
829           MUSA        HEB            HEB
829           FPUS        HEB            HEB

我的尝试:

UPDATE T1
SET ACCT_GRP_NM = 'HEB', 
WHERE ACCT_GRP_DESC = 'Z HEB' AND 
SET MAT_CO_CD ='FPUS', AND ACCT_GRP_CD ='829'
UPDATE T1
SET ACCT_GRP_NM = 'HEB',
ACCT_GRP_DESC = 'HEB'
WHERE mat_co_cd IN ('FPUS', 'MUSA')
AND acct_grp_cd = '829'
AND ACCT_GRP_DESC = 'Z HEB'

Answer 1

一种选择是使用merge:

:

SQL> select * from t1;
ACCT_GRP_CD MAT_CO_CD            ACCT_GRP_NM          ACCT_GRP_DESC
----------- -------------------- -------------------- --------------------
829 MUSA                 Z HEB                Z HEB
829 MUSA                 Z HEB                Z HEB

合并:

SQL> merge into t1
2    using (select a.rowid,
3             row_number() over (partition by acct_grp_cd order by a.rowid) rn
4           from t1 a
5          ) x
6    on (x.rowid = t1.rowid)
7    when matched then update set
8      t1.mat_co_cd = case when x.rn = 1 then 'MUSA'
9                          when x.rn = 2 then 'FPUS'
10                     end,
11      t1.acct_grp_nm = 'HEB',
12      t1.acct_grp_desc = 'HEB';
2 rows merged.

:后

SQL> select * from t1;
ACCT_GRP_CD MAT_CO_CD            ACCT_GRP_NM          ACCT_GRP_DESC
----------- -------------------- -------------------- --------------------
829 MUSA                 HEB                  HEB
829 FPUS                 HEB                  HEB
SQL>