一、 为什么会用？
在数据迁移的过程中，批量修改数据的几个字段。
二、思路：
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;
到这里就完成了，
要有什么更简单的方法，交流一下
