单条SQL如何按不同status值分别限制UPDATE更新行数

场景说明 #

你当前使用的批量更新语句逻辑为：从指定8类状态的记录中随机抽取500条，重置为NEW状态并归入1000号名单，代码如下：

UPDATE `vicidial_list` 
SET list_id = 1000, 
    status = "NEW", 
    called_since_last_reset = "N" 
WHERE status IN ("DROP","ERI","NRP","RPD","OKQ","PDROP","PI","RCAT")              
ORDER BY RAND() LIMIT 500;

由于各状态存量记录数差异极大（例如DROP状态共8917条，PI状态共59044条），上述写法会让存量大的状态占据绝大多数更新配额，无法满足各状态按固定比例更新的要求。你需要为每个状态单独设置更新行数上限，比如DROP每次最多更新20条，PI每次最多更新100条，保持各状态更新量稳定。
你已知可以拆分多条独立UPDATE语句实现该需求（示例中DROP的匹配条件漏写前引号，已修正）：

UPDATE `vicidial_list` 
SET list_id = 1000, 
    status = "NEW", 
    called_since_last_reset = "N" 
WHERE status = "DROP" 
ORDER BY RAND() LIMIT 20;

UPDATE `vicidial_list` 
SET list_id = 1000, 
    status = "NEW", 
    called_since_last_reset = "N" 
WHERE status = "PI" 
ORDER BY RAND() LIMIT 100;

单语句实现方案 #

MySQL 8.0+ 版本（支持窗口函数） #

通过窗口函数实现分组内随机排序编号，筛选符合配额要求的记录更新即可，单条语句可覆盖所有状态的配额限制：

UPDATE `vicidial_list` t
INNER JOIN (
    SELECT 
        lead_id, -- vicidial_list默认主键为lead_id，若你修改过主键请替换为对应字段
        ROW_NUMBER() OVER (
            PARTITION BY status 
            ORDER BY RAND()
        ) AS row_num
    FROM `vicidial_list`
    WHERE status IN ("DROP","ERI","NRP","RPD","OKQ","PDROP","PI","RCAT")
) tmp ON t.lead_id = tmp.lead_id
SET 
    t.list_id = 1000,
    t.status = "NEW",
    t.called_since_last_reset = "N"
WHERE 
    -- 下方按实际需求配置每个状态的最大更新行数即可
    (t.status = "DROP" AND tmp.row_num <= 20)
    OR (t.status = "PI" AND tmp.row_num <= 100)
    OR (t.status = "ERI" AND tmp.row_num <= 30)
    OR (t.status = "NRP" AND tmp.row_num <= 40)
    OR (t.status = "RPD" AND tmp.row_num <= 50)
    OR (t.status = "OKQ" AND tmp.row_num <= 60)
    OR (t.status = "PDROP" AND tmp.row_num <= 70)
    OR (t.status = "RCAT" AND tmp.row_num <= 80);

• 子查询中PARTITION BY status会按状态拆分数据分组，组内通过RAND()随机排序后生成连续行号，等价于给每个状态单独做了随机排名
• 外层通过主键关联回原表，通过WHERE条件限定每个状态只更新行号不超过预设配额的记录，完全满足分状态限数量、随机抽取的要求
• 后续调整配额时，只需要修改WHERE条件中对应状态的阈值数值即可，维护成本低

MySQL 5.x 版本（不支持窗口函数） #

可以通过用户变量模拟分组行号实现相同效果：

UPDATE `vicidial_list` t
INNER JOIN (
    SELECT 
        lead_id,
        @row_num := IF(@current_status = status, @row_num + 1, 1) AS row_num,
        @current_status := status
    FROM (
        SELECT lead_id, status 
        FROM `vicidial_list`
        WHERE status IN ("DROP","ERI","NRP","RPD","OKQ","PDROP","PI","RCAT")
        ORDER BY status, RAND()
    ) sorted_data,
    (SELECT @row_num := 0, @current_status := '') AS var_init
) tmp ON t.lead_id = tmp.lead_id
SET 
    t.list_id = 1000,
    t.status = "NEW",
    t.called_since_last_reset = "N"
WHERE 
    (t.status = "DROP" AND tmp.row_num <= 20)
    OR (t.status = "PI" AND tmp.row_num <= 100)
    -- 其余状态的配额规则按实际需求补充即可
;

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