如何将ROW_NUMBER()查询结果更新到数据表COUNTER列

你已经创建好COUNTER列的前提下，可以直接通过可更新CTE或者关联子查询的方式把窗口函数计算出的行号写入列中，不需要额外导出导入数据。

支持可更新CTE的数据库（MySQL 8.0+、SQL Server、PostgreSQL） #

直接运行以下语句即可完成更新：

WITH row_rank AS (
    SELECT
        COUNTER,
        ROW_NUMBER() OVER (PARTITION BY cons_id ORDER BY cons_id) AS calc_row_num
    FROM fy23_jul_aug_anniv_jv
)
UPDATE row_rank
SET COUNTER = calc_row_num;

通用兼容写法（适配Oracle、低版本MySQL等不支持可更新CTE的场景） #

需要通过表的唯一标识（主键/唯一键，若无主键可用cons_id, credited_date组合做唯一匹配）关联更新：

UPDATE fy23_jul_aug_anniv_jv t
JOIN (
    SELECT
        -- 请将id替换为你表中实际的主键字段名
        id,
        ROW_NUMBER() OVER (PARTITION BY cons_id ORDER BY cons_id) AS calc_row_num
    FROM fy23_jul_aug_anniv_jv
) r ON t.id = r.id
SET t.COUNTER = r.calc_row_num;

• 你当前窗口函数的排序逻辑存在缺陷：PARTITION BY cons_id后又用cons_id做排序，同一分组内所有行的排序值完全相同，最终生成的行号顺序是随机不稳定的。如果需要按入账日期排序编号，请将排序子句修改为ORDER BY credited_date
• 关联更新时必须确保关联条件能唯一匹配到单行，否则会出现数据更新错误
• 你原查询的输出参考如下：
  

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