mysql全表或批量更新某字段值&关联表查询更新修改表数据&数据补偿

牛刀小试：整表字段截取更新

-- 原型SQL
 select id, start_date, substring(start_date, 1, 10) as target_start_date from batch b
-- 时间转字符
date_format(r.start_time, '%Y-%m-%d')

• 原型截图
  
• 表字符截取

-- 实践SQL
update batch a 
inner join 
(select id, substring(start_date, 1, 10) as target_start_date from batch b ) c 
on a.id = c.id 
set a.start_date = c.target_start_date;

不开玩笑的：表字段值更新（多表关联）

• 需求
  两张表：目标表batch，原始表strategy
  batch表字段：id,strategy_id,…
  strategy表字段：id,word_id,…
  batch表新增字段；word_id，原strategy_id不需要

-- 实践SQL
UPDATE batch a
INNER JOIN 
( SELECT a1.id, s.word_id FROM batch a1 INNER JOIN strategy s WHERE a1.strategy_id = s.id ) c 
ON a.id = c.id 
SET a.word_id = c.word_id;