Azure Databricks SQL更新id列报错：用ROW_NUMBER替换id值失败

原SQL语句的核心问题在于：

• WHERE子句中的子查询没有与主表x.y建立关联关系，数据库无法将子查询生成的new_id对应到主表的具体行
• SET id = new_id中的new_id是子查询内部的列，主表无法直接引用这个未关联的子查询列

正确的SQL写法 #

方法1：使用CTE（公共表达式）关联更新 #

WITH updated_ids AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS new_id
    FROM x.y
)
UPDATE x.y
SET id = updated_ids.new_id
FROM x.y
JOIN updated_ids ON x.y.id = updated_ids.id;

方法2：子查询作为关联源表 #

UPDATE x.y
SET id = sub.new_id
FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS new_id
    FROM x.y
) sub
WHERE x.y.id = sub.id;

注意事项 #

• 如果id列是主键或存在唯一约束，更新前需要先移除约束，更新完成后再重新创建
• ORDER BY id确保行号生成的顺序稳定，如果原id存在重复值，建议添加额外的排序字段保证结果唯一
• 执行更新前可以先运行子查询/CTE部分，确认new_id的生成结果符合预期

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