求Oracle更新语句：标记重复记录失效并重定向子记录至有效记录

Let's break down the solution into two clear steps: first marking duplicate records in Table1 as inactive, then updating Table2 to link all related records to the valid entries in Table1.

1. Mark Duplicate Records in Table1 as Inactive #

We'll use a MERGE statement paired with a grouped subquery to identify which record to keep as valid for each (Name, PID) group. Based on your expected result, we'll retain the smallest ID for group 'A' and the largest ID for group 'B':

MERGE INTO Table1 t1
USING (
    SELECT Name, PID, 
           CASE WHEN Name = 'A' THEN MIN(ID) ELSE MAX(ID) END AS valid_id
    FROM Table1
    GROUP BY Name, PID
) vr
ON (t1.Name = vr.Name AND t1.PID = vr.PID)
WHEN MATCHED THEN
    UPDATE SET Active = CASE WHEN t1.ID = vr.valid_id THEN 'Y' ELSE 'N' END;

How this works: #

• The subquery vr calculates the valid ID for each group: for 'A' we pick the smallest ID, for other groups we pick the largest ID (this aligns perfectly with your desired outcome).
• The MERGE statement updates every row in Table1: sets Active to 'Y' only if the row is the valid ID for its group, otherwise sets it to 'N'.

2. Update Table2 to Link to Valid Table1 Records #

Next, we'll update Table2 so all records originally linked to any record in a group now point to the group's single valid ID:

WITH valid_records AS (
    SELECT Name, PID, 
           CASE WHEN Name = 'A' THEN MIN(ID) ELSE MAX(ID) END AS valid_id
    FROM Table1
    GROUP BY Name, PID
)
MERGE INTO Table2 t2
USING (
    SELECT t2.T2ID, vr.valid_id
    FROM Table2 t2
    JOIN Table1 t1 ON t2.CID = t1.ID
    JOIN valid_records vr ON t1.Name = vr.Name AND t1.PID = vr.PID
) src
ON (t2.T2ID = src.T2ID)
WHEN MATCHED THEN
    UPDATE SET t2.CID = src.valid_id;

How this works: #

• The CTE valid_records reuses the same grouping logic to get the valid ID per group, avoiding redundant code.
• We join Table2 with Table1 to map each CID to its corresponding group, then join with valid_records to get the target valid ID.
• The MERGE statement updates each row in Table2 to replace the original CID with the group's valid ID.

Quick Notes: #

• If you want a universal rule for all groups (e.g., always keep the smallest ID), replace the CASE statement with just MIN(ID). To always keep the largest ID, use MAX(ID) instead.
• Run the Table1 update first before updating Table2—it's not strictly required here, but it's good practice to ensure data consistency.

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