如何将含65957行数据表中半数行的processed字段设为5？

针对你的需求，这里分两种常见场景提供SQL解决方案（需替换your_table_name为实际表名）：

场景1：随机选取约半数行（不考虑person重复） #

直接随机筛选总行数的一半进行修改，同一个person可能部分记录被修改：

MySQL 写法 #

UPDATE your_table_name
SET processed = 5
WHERE processed = 0
ORDER BY RAND()
LIMIT FLOOR((SELECT COUNT(*) FROM your_table_name) / 2);

PostgreSQL 写法 #

UPDATE your_table_name
SET processed = 5
WHERE ctid IN (
    SELECT ctid
    FROM your_table_name
    WHERE processed = 0
    ORDER BY RANDOM()
    LIMIT (SELECT COUNT(*) FROM your_table_name) / 2
);

场景2：选取约半数person，修改其所有记录 #

保证同一个person的记录要么全改、要么全不改，避免拆分同一用户的记录：

MySQL 写法 #

UPDATE your_table_name t1
JOIN (
    SELECT DISTINCT person
    FROM your_table_name
    ORDER BY RAND()
    LIMIT FLOOR((SELECT COUNT(DISTINCT person) FROM your_table_name) / 2)
) t2 ON t1.person = t2.person
SET t1.processed = 5
WHERE t1.processed = 0;

PostgreSQL 写法 #

UPDATE your_table_name t1
SET processed = 5
WHERE processed = 0
AND person IN (
    SELECT DISTINCT person
    FROM your_table_name
    ORDER BY RANDOM()
    LIMIT (SELECT COUNT(DISTINCT person) FROM your_table_name) / 2
);

注意事项 #

• 执行前建议先用SELECT语句验证筛选结果，避免误操作
• 若表有主键，用主键代替ctid（仅PostgreSQL）会更可靠
• 行数为奇数时，FLOOR会取较小的半数，如需取较大半数可替换为CEIL

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