如何在Oracle表中按规则删除指定行并更新，能否仅用SQL实现

结论 #

完全可以仅通过标准SQL实现，不需要编写PL/SQL代码，仅需执行删除、更新两条SQL即可覆盖所有需求场景。

实现思路 #

首先通过窗口函数对每个Item Id分组计算特征值，再分别执行删除和更新操作：

• 分组统计每个Item Id下的Active状态记录数、Cancel状态记录数
• 对每个Item Id下的Cancel记录按Item Start Date升序排序，标记最早的Cancel记录序号为1
• 删除操作覆盖两个场景：
  • 删除所有无Active状态记录的Item Id对应的全部记录
  • 删除「有1条Active+多条Cancel」的Item Id下，除最早的1条Cancel之外的所有Cancel记录
• 更新操作仅覆盖「有1条Active+多条Cancel」的场景：将该场景下保留的唯一Cancel记录的Item End Date更新为31/Dec/3033
• 「1条Active+1条Cancel」的场景不会被删除和更新逻辑命中，保持原有数据不变

具体SQL代码 #

注：以下示例假设表名为ITEM_TABLE，Oracle环境下带空格的字段名需要用双引号包裹。

1. 执行删除操作 #

DELETE FROM ITEM_TABLE t
WHERE EXISTS (
    WITH item_stat AS (
        SELECT 
            "Item Id",
            "Item State",
            "Item Start Date",
            -- 统计当前Item Id下的Active记录数
            COUNT(CASE WHEN "Item State" = 'Active' THEN 1 END) OVER (PARTITION BY "Item Id") active_cnt,
            -- 统计当前Item Id下的Cancel记录数
            COUNT(CASE WHEN "Item State" = 'Cancel' THEN 1 END) OVER (PARTITION BY "Item Id") cancel_cnt,
            -- 给当前Item Id下的Cancel记录按开始时间升序排序，最早的序号为1
            ROW_NUMBER() OVER (PARTITION BY "Item Id", "Item State" ORDER BY "Item Start Date" ASC) cancel_rn
        FROM ITEM_TABLE
    )
    SELECT 1 FROM item_stat s
    WHERE s."Item Id" = t."Item Id"
      AND s."Item State" = t."Item State"
      AND s."Item Start Date" = t."Item Start Date"
      AND (
          -- 场景2：无Active记录的Item Id下所有记录全部删除
          s.active_cnt = 0
          -- 场景1：有1条Active+多条Cancel，删除除最早的1条Cancel之外的其余Cancel
          OR (s.active_cnt = 1 AND s.cancel_cnt > 1 AND s."Item State" = 'Cancel' AND s.cancel_rn > 1)
      )
);

2. 执行更新操作 #

UPDATE ITEM_TABLE t
SET "Item End Date" = DATE '3033-12-31'
WHERE EXISTS (
    WITH item_stat AS (
        SELECT 
            "Item Id",
            "Item State",
            COUNT(CASE WHEN "Item State" = 'Active' THEN 1 END) OVER (PARTITION BY "Item Id") active_cnt,
            COUNT(CASE WHEN "Item State" = 'Cancel' THEN 1 END) OVER (PARTITION BY "Item Id") cancel_cnt
        FROM ITEM_TABLE
    )
    SELECT 1 FROM item_stat s
    WHERE s."Item Id" = t."Item Id"
      AND s."Item State" = 'Cancel'
      AND s.active_cnt = 1
      AND s.cancel_cnt > 1
);

注意事项 #

• 执行任何修改操作前，请先备份全表数据，或者将DELETE/UPDATE语句改为SELECT语句，验证匹配到的记录是否符合预期，避免误操作
• 如果表中存在同一个Item Id、Item State、Item Start Date的重复记录，可以补充主键或唯一标识字段作为关联条件，避免匹配错误
• 执行完成后可自行按Item Id分组统计状态数量，验证清理结果是否符合需求规则

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