代码之家 › 专栏 › 技术社区 › NuCradle

如何在Oracle中用MERGE更新下表?

oracle12c sql-update merge oracle sql

NuCradle · 技术社区 · 6 年前

Table X
+---------+--------+---------------+--------+
| COLN    | COLM   | COLK          | COLP   |
+---------+--------+---------------+--------+
| 1       | 500    | K1            | 777    |
+---------+--------+---------------+--------+

Table A
+---------+--------+---------------+--------+
| COL1    | COL2   | COL3          | COL4   |
+---------+--------+---------------+--------+
| 1       | K1     | 500           | B      |
| 1       | K2     | 500           | NULL   |
+---------+--------+---------------+--------+

Table B
+---------+--------+---------+
| COLZ    | COLX   | COLW    |
+---------+--------+---------+
| 1       | K1     | 777     |
| 1       | K2     | 678     |
+---------+--------+---------+

这三个表有以下共同点:

X.COLN A.COL1 = B.COLZ X.COLk = A.COL2 B.COLX X.COLM = A.COL3

X.COLK , X.COLP B.COLX公司 , B.COLW

最终目标是,如果满足以下条件:

如果表A中有多条记录 A.COL1公司 的匹配(表X中有相应的记录)
A.COL4 = B ,另一个为空

我更新表X以替换 X.COLK公司 X.COLP公司 MERGE 表B中有值的语句( B.COLX公司 , 小马 --K2和678)。

MERGE INTO X FX
USING (
    SELECT COLX ONGOING_X, COLW ONGOING_W 
    FROM B 
    WHERE (COLZ, COLX) IN 
        (SELECT COL1, COL2 
         FROM A 
         WHERE COL3 = ? 
            AND COL1 = ? 
            AND COL4 IS NULL)
) NEW_B
    ON (FX.COLk = ?
        AND FX.COLP = ?)
WHEN MATCHED THEN
    UPDATE SET
        FX.COLk = NEW_B.ONGOING_X,
        FX.FOLP = NEW_B.ONGOING_W;

1 回复 | 直到 6 年前

Kaushik Nayak 6 年前

你可以做一个 MERGE 使用 ROWID .

MERGE INTO x tgt USING (
     WITH c AS (
          SELECT col1,
                 col3,
                 MAX(
                      CASE
                           WHEN col4 IS NULL THEN col2
                      END
                 ) AS col2 --Ongoing col2 as indicated from col4
          FROM a
          GROUP BY col1,
                   col3
          HAVING COUNT(
               CASE
                    WHEN col4 IS NULL THEN 1
               END
          ) = 1 AND COUNT(col4) = 1 --Contains one and exactly one NULL and one NON NULL
     ) SELECT x.rowid AS rid,
              b.*
       FROM x
       JOIN c ON c.col1 = x.coln AND c.col3 = x.colm 
       JOIN b ON b.colz = c.col1 AND b.colx = c.col2 --Join with ongoing value from c( a.k.a table A )
)
src ON ( tgt.rowid = src.rid ) --ROWID match
WHEN MATCHED THEN UPDATE SET tgt.colk = src.colx,
tgt.colp = src.colw;

Demo