Oracle更新表报no data found错误 按e_id规则更新active_flg

• 客户端工具：SQL Developer
• 数据库版本：Oracle 18c

测试表建表及初始化数据语句如下：

CREATE TABLE test_tab (
    s_id        NUMBER(10),
    e_id        NUMBER(10),
    active_flg  NUMBER(1)
);

INSERT INTO test_tab VALUES(1,11,1);
INSERT INTO test_tab VALUES(2,11,1);
INSERT INTO test_tab VALUES(3,11,0);
INSERT INTO test_tab VALUES(4,12,1);
INSERT INTO test_tab VALUES(5,12,1);

COMMIT;

更新test_tab表的active_flg字段，更新规则：

• 若同一个e_id对应的记录中存在任意一条active_flg为0，则将该e_id下所有记录的active_flg更新为0
• 若对应e_id下不存在active_flg=0的记录，则不执行更新操作

预期更新完成后表数据如下：

原有实现的PL/SQL代码如下：

SET SERVEROUTPUT ON;
DECLARE
lv_row test_tab%ROWTYPE;
BEGIN
FOR i IN (SELECT * FROM test_tab)
LOOP
SELECT * INTO lv_row FROM test_tab WHERE e_id = i.e_id AND active_flg = 0;
UPDATE test_tab SET active_flg = 0 WHERE active_flg = 0;
END LOOP;
END;
/

执行抛出no data found错误，且逻辑本身不满足需求，问题点如下：

• 异常触发原因：代码逐行遍历全表记录，当遍历到e_id=12的记录时，执行SELECT INTO语句查询当前e_id下active_flg=0的记录，而e_id=12分组下不存在该类记录，Oracle的SELECT INTO语句无返回结果时会直接抛出NO_DATA_FOUND异常。
• 更新逻辑错误：就算绕过异常，内部的UPDATE语句没有添加e_id匹配条件，只会反复更新全表中原本active_flg=0的记录，完全无法将同e_id下原本为1的记录更新为0。
• 实现效率低下：逐行循环处理的方式没有利用SQL的集合处理能力，数据量大时性能极差。

不需要编写复杂的PL/SQL循环，单条UPDATE语句即可直接实现需求，性能最优，逻辑清晰：

UPDATE test_tab t1
SET t1.active_flg = 0
WHERE EXISTS (
    SELECT 1
    FROM test_tab t2
    WHERE t2.e_id = t1.e_id
      AND t2.active_flg = 0
);
COMMIT;

语句逻辑说明：对表中每一条记录做判断，只要同e_id分组下存在任意一条active_flg=0的记录，就将当前记录的active_flg更新为0，不存在则跳过，完全匹配需求规则。执行后返回的结果和预期完全一致。

如果需要在PL/SQL块中执行（比如需要输出更新行数），可以直接封装上述UPDATE语句即可：

SET SERVEROUTPUT ON;
DECLARE
BEGIN
    UPDATE test_tab t1
    SET t1.active_flg = 0
    WHERE EXISTS (
        SELECT 1
        FROM test_tab t2
        WHERE t2.e_id = t1.e_id
          AND t2.active_flg = 0
    );
    DBMS_OUTPUT.PUT_LINE('更新记录数：' || SQL%ROWCOUNT);
    COMMIT;
END;
/

内容的提问来源于stack exchange，提问作者Vicky