单WHERE条件下批量更新多行不同Serial_No值的实现方案

现有门店业务表以order_id为数据标识字段，初始表结构及数据如下：

表中3条记录的order_id、erp_id字段值完全一致，当前Serial_No字段全为空值。现有匹配好的待更新数据源，需要将3条记录的Serial_No分别更新为不同指定值，要求所有更新操作仅使用同一组WHERE筛选条件，最终更新结果如下：

核心思路是给同组内的重复记录生成不重复的行定位标识，不需要为单条记录单独写WHERE条件，全量匹配同组数据后按行号对应赋值即可。

• 方案1：窗口函数行号匹配更新（适配MySQL 8.0+、PostgreSQL、SQL Server、Oracle等所有支持窗口函数的主流数据库）
  1. 先对待更新的Serial_No数据源做预处理，按order_id、erp_id分组，给同组内的每个Serial_No按指定匹配顺序生成连续行号rn，比如同组下第一个要匹配的Serial_No对应rn=1，第二个对应rn=2，以此类推。
  2. 更新时给原业务表的同组记录也用窗口函数，按业务确认的排序规则（比如主键、入库时间、物理存储顺序）生成相同规则的行号rn。
  3. 关联条件统一写WHERE 原表.order_id = 数据源.order_id AND 原表.erp_id = 数据源.erp_id AND 原表.rn = 数据源.rn，一次关联即可完成同组下所有不同Serial_No的赋值，不需要给单条记录单独加筛选条件。
     参考更新逻辑伪代码：

  WITH target_rn AS (
      SELECT 
          order_id,
          erp_id,
          Serial_No,
          ROW_NUMBER() OVER(PARTITION BY order_id, erp_id ORDER BY 排序字段,如主键id/创建时间) AS rn
      FROM 原业务表
      WHERE Serial_No IS NULL -- 统一筛选条件，仅更新待填充空值
  ),
  source_rn AS (
      SELECT
          order_id,
          erp_id,
          Serial_No,
          ROW_NUMBER() OVER(PARTITION BY order_id, erp_id ORDER BY Serial_No匹配顺序) AS rn
      FROM 待更新的Serial_No数据源
  )
  UPDATE target_rn
  SET Serial_No = source_rn.Serial_No
  FROM source_rn
  WHERE target_rn.order_id = source_rn.order_id
    AND target_rn.erp_id = source_rn.erp_id
    AND target_rn.rn = source_rn.rn;
  

• 方案2：用户变量行计数匹配（适配不支持窗口函数的旧版MySQL，比如5.x版本）
  逻辑和窗口函数方案一致，只是用用户变量手动实现同组内行号的生成，不需要依赖窗口函数能力，WHERE筛选条件依然全程统一，不需要单独给单条记录加过滤规则。
  参考伪代码：

  -- 给原表空值记录按分组生成连续行号
  SET @group_key := '', @rn :=0;
  UPDATE 原业务表
  SET rn = (
      SELECT @rn := IF(@group_key = CONCAT(order_id,'_',erp_id), @rn+1, 1),
             @group_key := CONCAT(order_id,'_',erp_id)
  )
  WHERE Serial_No IS NULL; -- 统一筛选条件
  
  -- 给待更新数据源生成同规则行号后，直接用和方案1一致的关联条件更新即可
  

• 方案3：临时表物理定位（适配所有数据库）
  如果数据库连用户变量、CTE能力都不支持，可以先查询出所有Serial_No IS NULL的同组记录，按匹配顺序把记录的主键/物理地址和待赋值的Serial_No一一对应存入临时表，最后用统一的WHERE 原表.主键 = 临时表.原表主键条件关联更新即可，全程不需要为单条Serial_No写单独的筛选规则。

注意：如果同组内记录没有明确的排序规则，需要提前确认Serial_No和原表记录的对应顺序要求，避免赋值错位。

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