CREATE TABLE t1(area_id NUMBER,parea_id NUMBER); --地域id,地域父id
CREATE TABLE t2(NAME VARCHAR2(10), area_id_old NUMBER, area_id_new NUMBER); --地域名称,地域旧编码,地域新编码
CREATE TABLE t3(area_id NUMBER ,other_id NUMBER)
---备份原始表
CREATE TABLE t1_bak AS SELECT * FROM t1;
CREATE TABLE t2_bak AS SELECT * FROM t2;
CREATE TABLE t3_bak AS SELECT * FROM t3;
-- 刷新数据
UPDATE T1
SET (T1.AREA_ID, T1.PAREA_ID) =
(SELECT T2.AREA_ID_NEW, TT.AREA_ID_NEW
FROM T2, T2 TT
WHERE T2.AREA_ID_OLD = T1.AREA_ID
AND TT.AREA_ID_OLD = T1.PAREA_ID);
UPDATE t3 SET t3.area_id=(SELECT area_id_new FROM t2 WHERE t2.area_id_old=t3.area_id);
CREATE TABLE t2(NAME VARCHAR2(10), area_id_old NUMBER, area_id_new NUMBER); --地域名称,地域旧编码,地域新编码
CREATE TABLE t3(area_id NUMBER ,other_id NUMBER)
---备份原始表
CREATE TABLE t1_bak AS SELECT * FROM t1;
CREATE TABLE t2_bak AS SELECT * FROM t2;
CREATE TABLE t3_bak AS SELECT * FROM t3;
-- 刷新数据
UPDATE T1
SET (T1.AREA_ID, T1.PAREA_ID) =
(SELECT T2.AREA_ID_NEW, TT.AREA_ID_NEW
FROM T2, T2 TT
WHERE T2.AREA_ID_OLD = T1.AREA_ID
AND TT.AREA_ID_OLD = T1.PAREA_ID);
UPDATE t3 SET t3.area_id=(SELECT area_id_new FROM t2 WHERE t2.area_id_old=t3.area_id);
---------------------------------------------------------
实际操作
select * from tempdy t;
UPDATE d_jc_dy T1
SET (T1.Id, T1.Pid) =
(SELECT T2.AREA_ID_NEW, TT.AREA_ID_NEW
FROM tempdy T2, tempdy TT
WHERE T2.AREA_ID_OLD = T1.Id
AND TT.AREA_ID_OLD = T1.Pid);
UPDATE d_jcz t3 SET t3.province_id=(SELECT area_id_new FROM tempdy t2 WHERE t2.area_id_old=t3.province_id);
UPDATE d_jc_dy T1
SET (T1.Id, T1.Pid) =
(SELECT T2.AREA_ID_NEW, TT.AREA_ID_NEW
FROM tempdy T2, tempdy TT
WHERE T2.AREA_ID_OLD = T1.Id
AND TT.AREA_ID_OLD = T1.Pid);
UPDATE d_jcz t3 SET t3.province_id=(SELECT area_id_new FROM tempdy t2 WHERE t2.area_id_old=t3.province_id);