MySQL如何实现更新表每行时随机从另一张表取对应字段值赋值

你写的SQL里的子查询(select col1, col2 from table2 ORDER BY RAND() limit 1) a2属于衍生表，在整个UPDATE语句执行阶段只会被计算一次，返回固定的单行结果，和table1的所有符合条件的行做笛卡尔积连接，所以所有行最终更新的都是同一个值。
即使你去掉LIMIT 1，MySQL的连接优化逻辑也会默认给每个table1行匹配到第一条符合条件的table2记录，最终还是所有行更新为同一值。

方案1：字段单独随机取值（无需保证col1、col2来自table2同一行） #

直接在SET子句里写子查询，每行更新时子查询都会单独执行一次，就能拿到不同的随机值：

UPDATE table1 a1
SET 
  col1 = (SELECT col1 FROM table2 ORDER BY RAND() LIMIT 1),
  col2 = (SELECT col2 FROM table2 ORDER BY RAND() LIMIT 1)
WHERE a1.col3 IS NOT NULL;

注意：该方案col1和col2可能来自table2的不同行，如果要求两个字段必须属于table2的同一条记录，请用方案2。

方案2：保证col1、col2来自table2同一行 #

MySQL 5.x版本写法 #

通过变量给两张表生成随机序号后关联匹配：

UPDATE table1 a1
-- 给table1符合条件的行生成随机序号
JOIN (
  SELECT id, @row1 := @row1 + 1 AS rn
  FROM table1
  WHERE col3 IS NOT NULL
  CROSS JOIN (SELECT @row1 := 0) init
) t1 ON a1.id = t1.id
-- 给table2生成随机序号，条数扩展到足够匹配table1的行数
JOIN (
  SELECT col1, col2, @row2 := @row2 + 1 AS rn
  FROM table2
  -- 如果table2行数少于table1符合条件的行数，可新增UNION项扩展条数
  CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) repeat_times
  CROSS JOIN (SELECT @row2 := 0) init
  ORDER BY RAND()
) t2 ON t1.rn = t2.rn
SET a1.col1 = t2.col1, a1.col2 = t2.col2;

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

用CTE+窗口函数实现更简洁：

WITH t1_rn AS (
  -- 给table1符合条件的行生成随机序号
  SELECT id, ROW_NUMBER() OVER (ORDER BY RAND()) rn
  FROM table1 WHERE col3 IS NOT NULL
),
t2_rn AS (
  -- 给table2生成随机序号，同时记录总行数
  SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY RAND()) rn,
         COUNT(*) OVER () total
  FROM table2
)
UPDATE table1 a1
JOIN t1_rn ON a1.id = t1_rn.id
-- 用取模逻辑实现table2行数不足时循环匹配
JOIN t2_rn ON t2_rn.rn = (t1_rn.rn MOD t2_rn.total) + 1
SET a1.col1 = t2_rn.col1, a1.col2 = t2_rn.col2;

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