在 Oracle 中一次对互补数据执行两个更新

Question

我需要更新下表：

USER:
| ID | ACTIVE | REF_COL | COL_2 | COL_3 |
|----|--------|---------|-------|-------|
| 1  |    1   |  value  | value | value |
| 2  |    0   |  value  | value | value |
| 3  |    1   |  value  | value | value |

我分别执行以下两个UPDATE语句：

1. 声明-1

   UPDATE
       USER
   SET
       ACTIVE = 1
   WHERE
       REF_COL IN (
           -- Subquery that generates a list of values
       )
   

2. 声明-2

   UPDATE
       USER
   SET
       ACTIVE = 0
   WHERE
       REF_COL NOT IN (
           -- Subquery that generates a list of values
       )
   

生成值列表的子查询对于两个UPDATE查询是相同的。

有什么方法可以帮助我立即执行查询，例如MERGE？

以下使用 MERGE 语句的查询无效：

MERGE INTO USER U
USING (
    -- Subquery that generates a list of values
) T
ON (U.REF_COL = T.VALUE)
WHEN MATCHED THEN
    UPDATE SET U.ACTIVE = 1
WHEN NOT MATCHED THEN
    UPDATE SET U.ACTIVE = 0

由于WHEN NOT MATCHED THEN条款期望有一个INSERT声明。

Answer 1

在

SET中使用CASE

UPDATE
    USER U
SET
    U.ACTIVE = (CASE WHEN U.REF_COL IN (<subquery>) THEN 0 ELSE 1 END)

演示

Answer 2

这是一个带有示例的merge版本。您必须将逻辑放在源部分中：

merge into users tgt
using (
  select u.ref_col, nvl2(s.ref_col, 1, 0) active
    from users u 
    left join subquery s on u.ref_col = s.ref_col ) src
on (tgt.ref_col = src.ref_col)
when matched then update set active = src.active;

^{DBfiddle 示例}

Answer 3

使用 Ponder Stibbons 在他的答案中提供的想法，我创建了以下查询; 我添加的额外逻辑是仅过滤需要更新ACTIVE值的那些行：

MERGE INTO USER U
USING (
    SELECT
        TGT.ID,
        SRC.ACTIVE
    FROM
        USER TGT
        JOIN (
            SELECT
                U.REF_COL,
                NVL2(T.REF_COL, 1, 0) AS ACTIVE
            FROM
                USER U
                LEFT JOIN (
                    -- Subquery that generates a list of values
                ) T ON T.REF_COL = U.REF_COL            
        ) SRC ON TGT.REF_COL = SRC.REF_COL
    WHERE
        TGT.ACTIVE != SRC.ACTIVE
) F
ON (U.ID = F.ID)
WHEN MATCHED THEN
    UPDATE SET
        U.ACTIVE = F.ACTIVE

Answer 4

用于合并的 pesudocode

：

MERGE into <target table>
    USING
        <souce table/view/result of subquery>
    ON
        <match condition>
    WHEN MATCHED THEN
        <update clause>
        <delete clause>
    WHEN NOT MATCHED THEN
        <insert clause>

Oracle Docs 是这样的：merge_insert_clause指定在 ON 子句的条件为 false 时要插入到目标表的列中的值。如果执行插入子句，则会激活在目标表上定义的所有插入触发器。如果省略 INSERT 关键字后的列列表，则目标表中的列数必须与 VALUES 子句中的值数匹配。 您不能使用合并或两个更新一个用于匹配，另一个用于不匹配。

您必须改为创建一个过程。

CREATE OR REPLACE PROCEDURE all_updates_in_user
IS
   cursor c1 is<<query that generates a list of values>>
BEGIN
  FOR rec in c1
   LOOP
   UPDATE
    USER
SET
    ACTIVE = 1
WHERE
    REF_COL IN rec.columnname;
   UPDATE
    USER
SET
    ACTIVE = 0
WHERE
    REF_COL  NOT IN rec.columnname;  
 END LOOP;
END;