如何将TABLE_B重复行关联ID替换为TABLE_A的首个唯一ID？

Got it, let's work through this step by step. The key here is that you can't delete duplicate rows in TABLE_A first—you need to fix the references in TABLE_B first, otherwise you'll lose the mapping between the old IDs and the one you want to keep.

Step 1: Create a mapping of each Value to the ID we want to retain in TABLE_A #

First, we need to identify which ID to keep for each unique Value in TABLE_A. Let's use a temporary table (or a CTE) to store this mapping—we'll use it to update TABLE_B later.

-- Create a temporary mapping table (works in SQL Server, PostgreSQL, etc.)
WITH ValueToKeep AS (
    SELECT 
        Id,
        Value,
        -- Assign a row number per Value group, ordered by Id (keeps the smallest Id per Value)
        ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Id) AS rn
    FROM TABLE_A
)
SELECT Id AS Keep_Id, Value INTO #IdMapping
FROM ValueToKeep
WHERE rn = 1;

Step 2: Update TABLE_B to point to the retained ID #

Now we'll use the mapping table to rewrite all Id_A values in TABLE_B to point to the single retained ID for their corresponding Value:

UPDATE b
SET b.Id_A = m.Keep_Id
FROM TABLE_B b
-- Join to TABLE_A to get the Value linked to each Id_A
JOIN TABLE_A a ON b.Id_A = a.Id
-- Join to our mapping table to find the ID we want to keep for that Value
JOIN #IdMapping m ON a.Value = m.Value
-- Only update rows that actually need changing
WHERE b.Id_A != m.Keep_Id;

Step 3: Delete duplicate rows from TABLE_A #

With TABLE_B fixed, we can safely remove the duplicate rows from TABLE_A, keeping only the IDs we mapped:

DELETE a
FROM TABLE_A a
JOIN #IdMapping m ON a.Value = m.Value
-- Delete all rows where the ID isn't the one we want to keep for its Value
WHERE a.Id != m.Keep_Id;

For MySQL users (no temporary tables needed, use CTEs directly) #

MySQL supports CTEs for updates/deletes in newer versions, so you can skip the temporary table:

-- Update TABLE_B first
WITH ValueToKeep AS (
    SELECT 
        Id,
        Value,
        ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Id) AS rn
    FROM TABLE_A
)
UPDATE TABLE_B b
JOIN TABLE_A a ON b.Id_A = a.Id
JOIN ValueToKeep m ON a.Value = m.Value AND m.rn = 1
SET b.Id_A = m.Id
WHERE b.Id_A != m.Id;

-- Then delete duplicates from TABLE_A
WITH ValueToKeep AS (
    SELECT 
        Id,
        Value,
        ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Id) AS rn
    FROM TABLE_A
)
DELETE a
FROM TABLE_A a
JOIN ValueToKeep m ON a.Value = m.Value AND m.rn = 1
WHERE a.Id != m.Id;

Quick note on your original DELETE statement #

Your initial query DELETE FROM TABLE_A WHERE ID NOT IN (SELECT distinct ID_A FROM TABLE_B) won't work here—every ID in TABLE_A exists in TABLE_B's Id_A column (per your sample data), so this would delete zero rows. We need to target duplicates per Value instead, which the above steps handle.

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