请求编写MariaDB SQL：删除attribute_lang重复名称并更新关联表

Let's tackle this problem step by step—we'll first update the related records in product_attribute to point to the retained IDs, then clean up the duplicates in attribute_lang. Wrapping everything in a transaction is a smart move here, so you can roll back if anything doesn't look right during testing.

Step 1: Update product_attribute to use the retained id_attribute #

First, we need to map every duplicate id_attribute to the smallest id_attribute value tied to its name. We'll use a subquery to grab the minimum ID per name, then join it to update the product references:

START TRANSACTION;

-- Update product_attribute to point to the minimal id_attribute for each duplicated name
UPDATE product_attribute pa
JOIN (
    -- Get the smallest id_attribute for each name that has duplicates
    SELECT name, MIN(id_attribute) AS min_id
    FROM attribute_lang
    GROUP BY name
    HAVING COUNT(*) > 1
) AS min_ids ON pa.id_attribute IN (
    -- Fetch all non-minimal id_attribute values for duplicated names
    SELECT al.id_attribute
    FROM attribute_lang al
    JOIN min_ids ON al.name = min_ids.name
    WHERE al.id_attribute != min_ids.min_id
)
JOIN attribute_lang al ON pa.id_attribute = al.id_attribute
SET pa.id_attribute = min_ids.min_id;

Step 2: Delete duplicate records from attribute_lang #

Now that all references in product_attribute are updated, we can safely remove the extra records from attribute_lang, keeping only the one with the smallest id_attribute for each name:

-- Delete duplicate records (keep only the minimal id_attribute per name)
DELETE al
FROM attribute_lang al
JOIN (
    SELECT name, MIN(id_attribute) AS min_id
    FROM attribute_lang
    GROUP BY name
    HAVING COUNT(*) > 1
) AS min_ids ON al.name = min_ids.name
WHERE al.id_attribute != min_ids.min_id;

-- Only commit if you've verified the changes are correct!
COMMIT;

Quick Tips: #

• Test first! Run the SELECT parts of the subqueries alone to see which records will be updated or deleted before executing the full transaction. For example, replace DELETE al with SELECT * to preview deletions.
• The HAVING COUNT(*) > 1 clause ensures we only target names that actually have duplicates, so we don't waste resources on unique entries.

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