如何使用序列号更新SQL表中的null值并解决序列不存在报错

你收到new_val不存在的报错，是因为不能直接通过序列名获取序列值，需要调用对应数据库的序列取值函数才能拿到递增值。另外如果要保证NULL值按id从小到大依次赋值为1、2、3，需要在更新时指定排序规则。

方案1：使用你已创建的序列（分数据库语法） #

PostgreSQL #

更新语句调整为：

UPDATE tb1
SET val = nextval('new_val')
WHERE val IS NULL
ORDER BY id;

Oracle #

更新语句调整为：

UPDATE tb1
SET val = new_val.NEXTVAL
WHERE val IS NULL
ORDER BY id;

方案2：无需创建序列，用窗口函数实现（兼容性更强，支持所有含窗口函数的数据库） #

无需提前创建序列，直接执行以下更新语句即可：

通用版本（PostgreSQL/SQL Server适用） #

WITH null_rows AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM tb1
    WHERE val IS NULL
)
UPDATE tb1
SET val = null_rows.rn
FROM null_rows
WHERE tb1.id = null_rows.id;

MySQL 8.0+ 版本 #

UPDATE tb1 t1
JOIN (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) rn
    FROM tb1
    WHERE val IS NULL
) t2 ON t1.id = t2.id
SET t1.val = t2.rn;

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