Mysql批量动态修改数据,用临时表的方法，三步解决。

一、为什么会用？在数据迁移的过程中，批量修改数据的几个字段。二、思路：1.把所要修改的数据查询出来。2.新建临时表，将查询出来的数据存进临时表中。3.将要查入数据的表和临时表做关联,将需要更新的字段进行修改。三、实现代码1.这是我的SQL逻辑，你们的根据你们的逻辑写就行了。select *from (select service.NAME as NAME, service.UNIT, t4.or

AzureBlue0001

4493人浏览 · 2022-04-27 14:12:21

AzureBlue0001 · 2022-04-27 14:12:21 发布

一、为什么会用？

在数据迁移的过程中，批量修改数据的几个字段。

二、思路：
1.把所要修改的数据查询出来。
2.新建临时表，将查询出来的数据存进临时表中。
3.将要查入数据的表和临时表做关联,将需要更新的字段进行修改。
三、实现代码
1.这是我的SQL逻辑，你们的根据你们的逻辑写就行了。

select *
from (select service.NAME as NAME 
           , service.UNIT
           , t4.org_name           as SUPPLY_NAME
           , '1'                   as flag
      from (
               select sazk.id,
                      IF(sazk.UNIT = '09', '03', sazk.UNIT) as UNIT,
                      t2.NAME 
               from (select id,
                            IF(SUBSTRING(UNIT, 1, 2) = '08', '03',
                               SUBSTRING(UNIT, 1, 2)) as UNIT
                     from table1
                    ) sazk
                        inner join table2 t2 ON sazk.id= t2.SUPP_ID
               group by t2.NAME 
               having count(1) > 1
           ) service
               left join table3 t4 ON service.UNIT= t4.org_no) mult
UNION ALL
(

    select service.NAME as NAME 
         , service.UNIT   as UNIT
         , t4.org_name           as SUPPLY_NAME
         , '2'                   as flag
    from (
             select sazk.id,
                    UNIT,
                    t2.NAME 
             from (select id,
                          SUBSTRING(UNIT, 1, 4) as UNIT
                   from table1
                  ) sazk
                      inner join table2 t2 ON sazk.id= t2.SUPP_ID
             group by t2.NAME 
             having count(1) = 1
         ) service
             inner join table3 t4 ON service.UNIT= t4.org_no
);

2.新建临时表，将上边的逻辑复制过来就好了

-- 因为测试的时候要看表是否存在
drop temporary table if exists serviceTemp;
-- 创建临时表
create temporary table serviceTemp
select *
from (select service.NAME as NAME 
           , service.UNIT
           , t4.org_name           as SUPPLY_NAME
           , '1'                   as flag
      from (
               select sazk.id,
                      IF(sazk.UNIT = '09', '03', sazk.UNIT) as UNIT,
                      t2.NAME 
               from (select id,
                            IF(SUBSTRING(UNIT, 1, 2) = '08', '03',
                               SUBSTRING(UNIT, 1, 2)) as UNIT
                     from table1
                    ) sazk
                        inner join table2 t2 ON sazk.id= t2.SUPP_ID
               group by t2.NAME 
               having count(1) > 1
           ) service
               left join table3 t4 ON service.UNIT= t4.org_no) mult
UNION ALL
(

    select service.NAME as NAME 
         , service.UNIT   as UNIT
         , t4.org_name           as SUPPLY_NAME
         , '2'                   as flag
    from (
             select sazk.id,
                    UNIT,
                    t2.NAME 
             from (select id,
                          SUBSTRING(UNIT, 1, 4) as UNIT
                   from table1
                  ) sazk
                      inner join table2 t2 ON sazk.id= t2.SUPP_ID
             group by t2.NAME 
             having count(1) = 1
         ) service
             inner join table3 t4 ON service.UNIT= t4.org_no
);

3.更新所修改的数据

update mk_temp t1,serviceTemp t2
set t1.UNIT     =t2.UNIT     ,
    t1.SUPPLY_NAME=t2.SUPPLY_NAME,
    t1.CREATE =t2.flag
where t1.SUP_NAME =t2.SUPPLIER_NAME;