MySQL无row_number() over partition时更新mmdocpositions错位position字段求助

语法错误原因 #

你写的UPDATE ... FROM是SQL Server、PostgreSQL等数据库支持的语法，MySQL的多表更新不支持该写法，需要改用MySQL原生的UPDATE ... JOIN语法。另外你当前的子查询没有返回原表的唯一主键，无法关联匹配到对应行完成更新，同时你只初始化了@row_number变量，未初始化@document_nr变量，可能会出现异常计算结果。

可行的更新方案 #

不需要编写存储过程，调整为如下写法即可运行，注意将代码中的主键字段名替换为你的mmdocpositions表实际的主键列名，同时补充同文档内的排序规则（示例用主键升序，你可以替换为创建时间等你需要的排序字段）：

-- 初始化两个会话变量
SET @row_number := 0;
SET @document_nr := '';

UPDATE mmdocpositions t1
JOIN (
    SELECT 
        主键字段名, -- 必须返回主键才能关联更新对应行
        @row_number := CASE
            WHEN @document_nr = document THEN @row_number + 1
            ELSE 1
        END AS num,
        @document_nr := document AS document
    FROM mmdocpositions 
    WHERE document IN (
        SELECT document FROM (
            SELECT document FROM mmdocpositions 
            WHERE POSITION='2'  
            GROUP BY document,POSITION 
            HAVING COUNT(*)>1
        ) AS tmp -- 加一层嵌套避免MySQL报错无法引用同表更新
    ) 
    ORDER BY document, 主键字段名 ASC -- 同文档内按主键升序生成序号，可改为你需要的排序规则
) AS t2 ON t1.主键字段名 = t2.主键字段名
SET t1.POSITION = t2.num;

注意事项 #

• 执行更新前请先备份整张表，避免误操作导致数据损坏，你可以先执行子查询部分确认生成的num和对应行匹配无误再执行更新
• 如果你的POSITION字段是数值类型，建议将条件里的POSITION='2'改为POSITION=2，避免隐式类型转换影响查询效率

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