如何修复SQL表中同一名称对应多NameID的问题（C#实现）

核心思路 #

先找出每个名称对应的最小NameID，将该名称下所有其他NameID统一替换为这个最小值，最后清理重复记录（若有）。如果存在关联其他表的情况，需同步更新关联表中的NameID。

SQL 处理逻辑 #

1. 排查重复名称及目标ID #

执行以下查询，获取所有存在多ID的名称，以及它们要统一到的最小NameID：

SELECT Name, MIN(NameID) AS TargetNameID
FROM YourTableName
GROUP BY Name
HAVING COUNT(DISTINCT NameID) > 1;

2. 更新主表NameID #

将所有非最小ID的记录，NameID替换为对应名称的最小ID：

UPDATE t1
SET t1.NameID = t2.TargetNameID
FROM YourTableName t1
JOIN (
    SELECT Name, MIN(NameID) AS TargetNameID
    FROM YourTableName
    GROUP BY Name
) t2 ON t1.Name = t2.Name
WHERE t1.NameID != t2.TargetNameID;

3. 同步更新关联表（如存在） #

如果有其他表（例如Orders）关联了NameID，需同步更新这些表的关联字段：

UPDATE o
SET o.NameID = t.TargetNameID
FROM Orders o
JOIN (
    SELECT Name, MIN(NameID) AS TargetNameID
    FROM YourTableName
    GROUP BY Name
) t ON EXISTS (
    SELECT 1 FROM YourTableName nt WHERE nt.NameID = o.NameID AND nt.Name = t.Name
)
WHERE o.NameID != t.TargetNameID;

4. 删除主表重复记录 #

更新后会出现同一名称+同一ID的重复记录，执行以下语句清理：

WITH RankedRecords AS (
    SELECT 
        NameID, 
        Name,
        ROW_NUMBER() OVER (PARTITION BY Name, NameID ORDER BY NameID) AS RowNum
    FROM YourTableName
)
DELETE FROM RankedRecords
WHERE RowNum > 1;

C# 代码实现 #

完整代码片段（含事务保障） #

using System;
using System.Data.SqlClient;

public class NameIdUnifier
{
    public static void UnifyNameIds()
    {
        string connectionString = "你的数据库连接字符串";
        
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlTransaction transaction = conn.BeginTransaction())
            {
                try
                {
                    // 1. 记录要统一的名称与目标ID（可选，用于日志）
                    string logQuery = @"SELECT Name, MIN(NameID) AS TargetNameID FROM YourTableName GROUP BY Name HAVING COUNT(DISTINCT NameID) > 1";
                    using (SqlCommand logCmd = new SqlCommand(logQuery, conn, transaction))
                    {
                        using (SqlDataReader reader = logCmd.ExecuteReader())
                        {
                            Console.WriteLine("待统一的名称及目标ID：");
                            while (reader.Read())
                            {
                                string name = reader["Name"].ToString();
                                int targetId = (int)reader["TargetNameID"];
                                Console.WriteLine($"- {name} → {targetId}");
                            }
                        }
                    }

                    // 2. 更新主表
                    string updateMainTable = @"UPDATE t1 SET t1.NameID = t2.TargetNameID FROM YourTableName t1 JOIN (SELECT Name, MIN(NameID) AS TargetNameID FROM YourTableName GROUP BY Name) t2 ON t1.Name = t2.Name WHERE t1.NameID != t2.TargetNameID";
                    using (SqlCommand mainCmd = new SqlCommand(updateMainTable, conn, transaction))
                    {
                        int mainAffected = mainCmd.ExecuteNonQuery();
                        Console.WriteLine($"主表更新完成，共修改 {mainAffected} 条记录");
                    }

                    // 3. 更新关联表（如果有，注释掉不需要的部分）
                    string updateRelatedTable = @"UPDATE o SET o.NameID = t.TargetNameID FROM Orders o JOIN (SELECT Name, MIN(NameID) AS TargetNameID FROM YourTableName GROUP BY Name) t ON EXISTS (SELECT 1 FROM YourTableName nt WHERE nt.NameID = o.NameID AND nt.Name = t.Name) WHERE o.NameID != t.TargetNameID";
                    using (SqlCommand relatedCmd = new SqlCommand(updateRelatedTable, conn, transaction))
                    {
                        int relatedAffected = relatedCmd.ExecuteNonQuery();
                        Console.WriteLine($"关联表更新完成，共修改 {relatedAffected} 条记录");
                    }

                    // 4. 删除重复记录
                    string deleteDuplicates = @"WITH RankedRecords AS (SELECT NameID, Name, ROW_NUMBER() OVER (PARTITION BY Name, NameID ORDER BY NameID) AS RowNum FROM YourTableName) DELETE FROM RankedRecords WHERE RowNum > 1";
                    using (SqlCommand deleteCmd = new SqlCommand(deleteDuplicates, conn, transaction))
                    {
                        int deleteAffected = deleteCmd.ExecuteNonQuery();
                        Console.WriteLine($"重复记录清理完成，共删除 {deleteAffected} 条记录");
                    }

                    // 提交事务
                    transaction.Commit();
                    Console.WriteLine("所有操作执行成功！");
                }
                catch (Exception ex)
                {
                    // 回滚事务
                    transaction.Rollback();
                    Console.WriteLine($"操作失败，已回滚：{ex.Message}");
                }
            }
            conn.Close();
        }
    }
}

注意事项 #

• 操作前务必备份数据库，避免数据丢失
• 生产环境下必须使用事务，确保所有操作原子性（要么全部成功，要么全部回滚）
• 替换代码中的YourTableName为实际表名，Orders为实际关联表名，连接字符串改为你的数据库配置

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