create table a1(TP_END_DATE NUMBER,WK_END_DATE NUMBER,
MTH_START_DATE NUMBER,
MTH_END_DATE NUMBER,
TIME_PERD_START_DATE NUMBER);
create table a2(TP_END_DATE DATE ,WK_END_DATE DATE,MTH_START_DATE DATE,MTH_END_DATE DATE,DAY_DATE DATE) ;
SQL> select * from a1;
TP_END_DATE WK_END_DATE MTH_START_DATE MTH_END_DATE TIME_PERD_START_DATE
----------- ----------- -------------- ------------ --------------------
19990101 19990101 19990101 19990101 29990101
SQL> select * from a2;
TP_END_DA WK_END_DA MTH_START MTH_END_D DAY_DATE
--------- --------- --------- --------- ---------
01-JAN-99 01-JAN-99 01-JAN-99 01-JAN-99 01-JAN-99
BEGIN
Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
TP_END_DATE =
( select TO_NUMBER(TO_CHAR((TP_END_DATE),'YYYYMMDD')) from CAL_MASTR_DIM b
where a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));
Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
WK_END_DATE =
( select TO_NUMBER(TO_CHAR((WK_END_DATE),'YYYYMMDD') from CAL_MASTR_DIM b where
a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));
Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
MTH_START_DATE =
( select TO_NUMBER(TO_CHAR((MTH_START_DATE),'YYYYMMDD') from CAL_MASTR_DIM b where
a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));
Update ADWGU_DICFR.DICFR_ORDR_CUT_FCT a
Set
MTH_END_DATE =
( select TO_NUMBER(TO_CHAR((MTH_END_DATE),'YYYYMMDD') from CAL_MASTR_DIM b where
a.TIME_PERD_START_DATE= TO_NUMBER(TO_CHAR((b.DAY_DATE),'YYYYMMDD')));
COMMIT;
END;
/
改为merge：
merge into a1 a
using a2 b
on (a.TIME_PERD_START_DATE = TO_NUMBER(TO_CHAR((b.DAY_DATE), 'YYYYMMDD')))
WHEN MATCHED THEN
UPDATE
SET a.TP_END_DATE = TO_NUMBER(TO_CHAR((b.TP_END_DATE), 'YYYYMMDD')),
a.WK_END_DATE = TO_NUMBER(TO_CHAR((b.WK_END_DATE),'YYYYMMDD')),
a.MTH_START_DATE = TO_NUMBER(TO_CHAR((b.MTH_START_DATE),'YYYYMMDD')),
a.MTH_END_DATE = TO_NUMBER(TO_CHAR((b.MTH_END_DATE),'YYYYMMDD'))
