Oracle如何为已存在的表新增列并按指定列排序填充自增ID

问题解决方法 #

你已经完成了新增ID列的操作：ALTER TABLE TEST ADD ID INTEGER;，接下来可通过以下两种方式实现按COL列排序分配步长为1的自增ID：

方法1：MERGE语句（兼容性最高，无额外表约束要求） #

MERGE INTO TEST t1
USING (
    SELECT 
        ROWID AS row_key,
        ROW_NUMBER() OVER(ORDER BY COL ASC) AS sorted_id
    FROM TEST
) t2
ON (t1.ROWID = t2.row_key)
WHEN MATCHED THEN UPDATE SET t1.ID = t2.sorted_id;

实现逻辑：内层查询先按COL列排序，为每行生成连续序号，同时获取每行唯一标识ROWID，再通过ROWID关联原表完成更新，完全匹配需求。

方法2：可更新视图（适合表存在主键/唯一约束的场景） #

UPDATE (
    SELECT 
        ID,
        ROW_NUMBER() OVER(ORDER BY COL ASC) AS sorted_id
    FROM TEST
)
SET ID = sorted_id;

原有写法错误原因 #

• 直接执行UPDATE TEST SET ID = ROWNUM;：Oracle的ROWNUM是在结果集排序前分配的伪列，不会按照COL的排序规则生成序号
• UPDATE TEST SET ID = (SELECT ROWNUM FROM TEST ORDER BY COL);：子查询返回多行结果，无法直接赋值给单行的ID字段，触发单行子查询返回多行报错
• CTE写法错误：Oracle 12c之前不支持WITH子句后直接跟UPDATE语句，同时写反了赋值方向，且ROWNUM是系统伪列不支持被直接赋值，因此触发missing SELECT keyword报错。

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