我现在批量往数据库里导正式数据(sql insert),但是数据中有三列分别是岗位,办事处,大区,给的数据中只给了岗位的值,办事处的值可以通过岗位值在岗位表查到,大区的值可以通过办事处的值在办事处表里查到。现在我已经把其他数据都导进去了,只剩办事处和大区没有值,我该如何批量更新这两列的值啊
导入的数据的表:
岗位表:
办事处表:
本人sql不是很好,希望sql大神能给出来解惑一下,拜谢~
适用于 mysql
update tb_import as i inner join (
select s.station_code, s.office_code, o.zone_code from tb_station as s
inner join tb_office as o on s.office_code=o.office_code
) as z
set i.office_code=z.office_code, i.zone_code=z.zone_code
适用于 mssql postgresql
update tb_import set office_code=z.office_code, zone_code=z.zone_code from (
select s.station_code, s.office_code, o.zone_code from tb_station as s
inner join tb_office as o on s.office_code=o.office_code
) as z
where tb_import.station_code=z.station_code
UPDATE TABLE1 t1, TABLE2 t2 SET t1.OFFICE_CODE=t2.OFFICE_CODE WHERE t1.STATION_CODE=t2.STATION_CODE;
//更新办事处: UPDATE 导入的数据的表 AS a JOIN 岗位表 AS b SET a.OFFICE_CODE = b.OFFICE_CODE WHERE a.STATION_CODE = b.STATION_CODE //更新大区: UPDATE 导入的数据的表 AS a JOIN 办事处表 AS b SET a.ZONE_CODE = b.ZONE_CODE WHERE a.OFFICE_CODE = b.OFFICE_CODE