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

  • Post author:
  • Post category:mysql


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;



版权声明:本文为JzCm__原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。