将查询重复名称的SELECT语句转换为UPDATE语句求助

No worries, let's turn that SELECT query into a working UPDATE that renames duplicates into the name(1), name(2) format you need. Here's how to do it, depending on your SQL dialect:

Generic SQL (PostgreSQL, SQL Server, etc.) #

First, we'll use a Common Table Expression (CTE) to generate a unique row number for each duplicate name, then join that back to your table to update the names:

WITH numbered_duplicates AS (
    SELECT
        id,
        name,
        -- Assign a sequential number to each row in the same name group
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
    FROM your_table
)
UPDATE your_table t
SET name = CONCAT(t.name, '(', nd.row_num, ')')
FROM numbered_duplicates nd
WHERE t.id = nd.id
-- Only update rows where the name has duplicates
AND (SELECT COUNT(*) FROM your_table WHERE name = t.name) > 1;

MySQL Specific Syntax #

MySQL uses a slightly different UPDATE JOIN syntax, so adjust it like this:

UPDATE your_table t
INNER JOIN (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_num
    FROM your_table
) nd ON t.id = nd.id
SET t.name = CONCAT(t.name, '(', nd.row_num, ')')
WHERE (SELECT COUNT(*) FROM your_table WHERE name = t.name) > 1;

Handling Existing Numbered Names #

If some of your names already have (X) suffixes (like name(1)), you'll want to strip those first to avoid messy results like name(1)(2). Modify the CONCAT line with a regex replace:

-- For PostgreSQL/SQL Server
SET name = CONCAT(REGEXP_REPLACE(t.name, '\(\d+\)$', ''), '(', nd.row_num, ')')

-- For MySQL (uses different regex escape syntax)
SET name = CONCAT(REGEXP_REPLACE(t.name, '\\([0-9]+\\)$', ''), '(', nd.row_num, ')')

Quick Tips: #

• Replace your_table with your actual table name, and id with your primary key (or any unique column that identifies each row).
• The ORDER BY id in the window function ensures consistent numbering—swap it with another column (like a creation timestamp) if you want to order duplicates differently.
• The final WHERE clause ensures we only touch rows with duplicate names, leaving unique names unchanged.

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