能否通过UPDATE FROM SELECT实现列自增更新，避免循环单条更新？

完全可以用单条UPDATE语句结合子查询实现批量更新，避免代码层遍历数千条记录执行单条更新的低效操作。

核心思路 #

通过子查询给每个player_id生成递增序号，再关联player_squad表完成批量赋值，无需在代码中循环执行单条更新。

不同数据库的实现方案 #

不同数据库生成自增序号的语法略有差异，以下是常见场景的写法：

1. PostgreSQL

利用ROW_NUMBER()窗口函数生成序号，关联两张表完成更新：

UPDATE player_squad ps
SET player_position = p.row_num
FROM (
    SELECT player_id, ROW_NUMBER() OVER (ORDER BY player_id) AS row_num
    FROM players
    -- 可添加WHERE条件筛选需要更新的玩家，例如 WHERE team_id = 123
) p
WHERE ps.player_id = p.player_id;

2. MySQL 8.0+

支持窗口函数，写法类似：

UPDATE player_squad ps
JOIN (
    SELECT player_id, ROW_NUMBER() OVER (ORDER BY player_id) AS row_num
    FROM players
    -- 可添加筛选条件
) p ON ps.player_id = p.player_id
SET ps.player_position = p.row_num;

3. MySQL 5.x（不支持窗口函数）

使用用户变量生成自增序号：

SET @row_num = 0;
UPDATE player_squad ps
JOIN (
    SELECT player_id, (@row_num := @row_num + 1) AS row_num
    FROM players
    ORDER BY player_id -- 必须添加排序保证序号稳定
    -- 可添加筛选条件
) p ON ps.player_id = p.player_id
SET ps.player_position = p.row_num;

关键注意事项 #

• 子查询中必须添加ORDER BY（比如按player_id排序），否则生成的序号顺序不确定，会导致player_position值混乱。
• 如果仅需更新特定团队的玩家，在子查询的FROM players后追加WHERE team_id = 目标团队ID即可，无需全表更新。
• 执行前可单独运行子查询，确认序号与player_id的对应关系正确，避免误更新。

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