不支持CTE+UPDATE时，如何批量更新自增secondaryId字段

算力阿峰

2026-5-27

嘿，既然你当前环境不支持原来的CTE+UPDATE写法，我给你几个兼容性拉满的替代方案，能轻松实现给secondaryId为0的行分配递增唯一值的需求～

替代解决方案：给secondaryId为0的行分配递增唯一值

方案1：临时表+会话变量（适合MySQL、MariaDB等）

这个方案先把需要更新的行和对应的新数值存入临时表，再批量更新原表，逻辑清晰且兼容性极强：

先获取当前secondaryId的最大值（处理全0的边界情况）：

SELECT COALESCE(MAX(secondaryId), 0) INTO @max_sec_id FROM Sample;

用COALESCE是为了避免所有行secondaryId都是0时，MAX返回NULL导致后续计算出错。

创建临时表存储待更新行的ID和新的secondaryId：

CREATE TEMPORARY TABLE temp_updates AS
SELECT 
    id,
    @max_sec_id := @max_sec_id + 1 AS new_secondaryId
FROM Sample
WHERE secondaryId = 0
ORDER BY crtdate; -- 按创建时间排序，保证新数值严格遵循行的创建顺序递增

批量更新原表：

UPDATE Sample s
JOIN temp_updates tu ON s.id = tu.id
SET s.secondaryId = tu.new_secondaryId;

方案2：CTE+窗口函数（适合PostgreSQL、SQL Server等，若环境支持基础CTE）

如果你的数据库支持CTE但不支持原写法的联动更新，可以试试这个更简洁的版本：

WITH max_sec AS (
    SELECT COALESCE(MAX(secondaryId), 0) AS max_val FROM Sample
),
updates AS (
    SELECT 
        id,
        max_val + ROW_NUMBER() OVER (ORDER BY crtdate) AS new_secondaryId
    FROM Sample, max_sec
    WHERE secondaryId = 0
)
UPDATE Sample s
SET secondaryId = u.new_secondaryId
FROM updates u
WHERE s.id = u.id;