Oracle合并流程中客户记录删除与主记录保留的逻辑问题求助

主表TableA无删除标识字段，只能依赖rowid处理记录删除。

表结构与初始数据 #

TableA（主表） #

TableB（审计表） #

在Oracle Forms中搜索ABCDEFGH时，匹配记录存入此表：

TableC（客户审计表） #

仅存储客户编号及标识字段，每个客户仅一条记录：

注：TableB和TableC为审计表，无法删除其中数据。

现有正确逻辑（去重保留） #

使用游标获取每个customer_no||identitycode||identitynumber组合下，对应TableB.maintain=Yes且TableC.maintain=Y的最小rowid记录，循环删除其他重复记录：

cursor cr_save_Y is
      SELECT  y.customer_no, y.identitycode, y.identitynumber, MIN(z.rowid) row_idn
        FROM  TableC x,
              TableB   y,
              TableA z
        WHERE x.customer_no     = y.customer_no
        AND   x.n_merge_seq  = y.n_merge_seq
        AND   y.identitycode       = z.identitycode
        AND   y.identitynumber         = z.identitynumber
        AND   y.customer_no     = z.customer_no
        AND   NVL(y.maintain,'Y')='Y'     -- 需要保留的标识记录
        AND   x.n_merge_seq   = 1
GROUP BY y.customer_no,
      y.identitycode,
      y.identitynumber   ;

for i in cr_save_Y
loop
    delete from TableA
    where customer_no = i.customer_no
    and identitycode = i.identitycode 
    and identityNumber = i.identityNumber 
    and rowid <> i.row_idn;
end loop;  

这部分逻辑正常，能保留每个需保留组合的唯一记录。

删除逻辑的问题 #

需要实现：删除TableA中对应TableB.maintain=N且满足TableC.maintain=Y或TableC.remove=Y的记录，但现有两种方案都有问题：

方案1（错误） #

原逻辑会导致TableA中1234||passport||ABCDEFGH的记录被全部删除，因为TableB存在该组合maintain=N的记录：

delete from TableA
where customer_no||identitycode||identitynumber in (
    select y.customer_no||y.identitycode||y.identitynumber
    from TableC x,TableB y
    where x.customer_no=y.customer_no
    and x.n_merge_seq=y.n_merge_seq
    and nvl(y.maintain,'N')='N'
    and x.n_merge_seq=1 
    and (x.maintain = 'Y' or x.remove='Y')
); 

方案2（错误） #

仅匹配TableC.remove=Y时，无法删除场景2中1234||adhar||1234 5678 9123的记录：

delete from TableA
where customer_no||identitycode||identitynumber in (
    select y.customer_no||y.identitycode||y.identitynumber
    from TableC x,TableB y
    where x.customer_no=y.customer_no
    and x.n_merge_seq=y.n_merge_seq
    and nvl(y.maintain,'N')='N'
    and x.n_merge_seq=1 
    and (x.remove='Y')
);

场景需求 #

场景1期望输出（TableA最终数据） #

场景2期望输出（TableA最终数据） #

解决方案 #

核心思路是区分客户级别的操作和标识级别的操作：

1. 对于TableC中Remove='Y'的客户，直接删除其在TableA中的所有记录；
2. 对于TableC中Maintain='Y'的客户，仅删除其在TableB中Maintain='N'对应的标识记录。

可以合并为一条删除语句，避免分两次操作的冲突：

DELETE FROM TableA z
WHERE EXISTS (
    SELECT 1
    FROM TableC x
    JOIN TableB y 
        ON x.customer_no = y.customer_no 
        AND x.n_merge_seq = y.n_merge_seq
    WHERE z.customer_no = y.customer_no
      AND z.identitycode = y.identitycode
      AND z.identitynumber = y.identitynumber
      AND NVL(y.maintain, 'N') = 'N'
      AND x.n_merge_seq = 1
      -- 条件拆分：要么客户被标记删除，要么客户需维护但当前标识需删除
      AND (x.Remove = 'Y' OR (x.Maintain = 'Y' AND y.Remove = 'Yes'))
);

逻辑说明 #

• 当TableC的客户Remove='Y'时，该客户所有在TableB中Maintain='N'的标识记录都会被删除（实际是该客户全部记录，因为TableB包含该客户所有匹配记录）；
• 当TableC的客户Maintain='Y'时，仅删除TableB中Maintain='N'且Remove='Yes'的标识记录，不会影响该客户其他需保留的标识；
• 使用EXISTS而非字符串拼接的IN子句，避免字段拼接可能的冲突（比如特殊字符导致的匹配错误），同时性能更优。

另外，也可以将删除逻辑和之前的去重逻辑合并，进一步优化：

-- 先去重再删除，或者合并操作
WITH keep_records AS (
    SELECT MIN(z.rowid) AS keep_rowid
    FROM TableC x
    JOIN TableB y 
        ON x.customer_no = y.customer_no 
        AND x.n_merge_seq = y.n_merge_seq
    JOIN TableA z 
        ON y.customer_no = z.customer_no
        AND y.identitycode = z.identitycode
        AND y.identitynumber = z.identitynumber
    WHERE NVL(y.maintain, 'Y') = 'Y'
      AND x.n_merge_seq = 1
      AND x.Maintain = 'Y'
    GROUP BY y.customer_no, y.identitycode, y.identitynumber
)
DELETE FROM TableA z
WHERE rowid NOT IN (SELECT keep_rowid FROM keep_records)
AND EXISTS (
    SELECT 1
    FROM TableC x
    JOIN TableB y 
        ON x.customer_no = y.customer_no 
        AND x.n_merge_seq = y.n_merge_seq
    WHERE z.customer_no = y.customer_no
      AND z.identitycode = y.identitycode
      AND z.identitynumber = y.identitynumber
      AND x.n_merge_seq = 1
      AND (x.Remove = 'Y' OR NVL(y.maintain, 'N') = 'N')
);

这个合并语句先确定所有需要保留的记录rowid，再删除不在保留列表且符合删除条件的记录，一次性完成去重和删除操作。

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