orcal update

orcal update

这里是以自身规则更新自己表

假如利用备份表来更新原表，需要where exists来保证出现没有更新成功的数据变为空的情况

update DAM_PRVINCETR a set a.CITY_CODE = (
select b.CITY_CODE from (SELECT * FROM (
SELECT PARENT_ID,CONCAT(substr(CITY_CODE,0, 2) ,'0000000000') as city_code 
FROM "DAM_PRVINCETR" WHERE PARENT_ID in(SELECT CITY_ID FROM DAM_PRVINCETR WHERE PARENT_ID=0)
) GROUP BY PARENT_ID,CITY_CODE) b where a.CITY_ID=b.PARENT_ID
)
where exists(
select * from (SELECT * FROM (
SELECT PARENT_ID,CONCAT(substr(CITY_CODE,0, 2) ,'0000000000') as city_code 
FROM "DAM_PRVINCETR" WHERE PARENT_ID in(SELECT CITY_ID FROM DAM_PRVINCETR WHERE PARENT_ID=0)
) GROUP BY PARENT_ID,CITY_CODE) b where a.CITY_ID=b.PARENT_ID
)