编写SQL Update脚本设置ParentID：关联同Desc项至首个实例

原始数据表 #

目标效果 #

执行更新后，每个唯一Desc的首个实例（ID最小的记录）保留ParentID=0，其余同Desc的记录ParentID设为该首个实例的ID：

SQL 更新脚本 #

以下是针对不同数据库的实现方案，需将your_table替换为你的实际表名：

通用方案（适配MySQL、SQL Server、PostgreSQL等） #

通过子查询获取每个Desc对应的最小ID（首个实例），再关联更新：

UPDATE your_table t1
JOIN (
    SELECT `Desc`, MIN(ID) AS first_id
    FROM your_table
    GROUP BY `Desc`
) t2 ON t1.`Desc` = t2.`Desc`
SET t1.ParentID = t2.first_id
WHERE t1.ID != t2.first_id;

注：若Desc是数据库关键字，需用反引号（MySQL）或方括号（SQL Server）/双引号（PostgreSQL）包裹，比如[Desc]或"Desc"

MySQL 简化写法 #

UPDATE your_table t1,
(SELECT `Desc`, MIN(ID) AS first_id FROM your_table GROUP BY `Desc`) t2
SET t1.ParentID = t2.first_id
WHERE t1.`Desc` = t2.`Desc` AND t1.ID != t2.first_id;

SQL Server 写法 #

UPDATE t1
SET ParentID = t2.first_id
FROM your_table t1
INNER JOIN (
    SELECT [Desc], MIN(ID) AS first_id
    FROM your_table
    GROUP BY [Desc]
) t2 ON t1.[Desc] = t2.[Desc]
WHERE t1.ID != t2.first_id;

PostgreSQL 写法 #

UPDATE your_table t1
SET ParentID = t2.first_id
FROM (
    SELECT "Desc", MIN(ID) AS first_id
    FROM your_table
    GROUP BY "Desc"
) t2
WHERE t1."Desc" = t2."Desc" AND t1.ID != t2.first_id;

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