ORACLE 兩個表結構相同,一個做為源表,一個做為補錄表,同步補錄表資料到源表的SQL語句

表結構
-- Create table 源表
create table INPUT_FWMJ
(
  JGBH CHAR(5) not null,
  JGMC VARCHAR2(50) not null,
  BMBH CHAR(10) not null,
  BMMC VARCHAR2(50) not null,
  FWMJ NUMBER(20,2) not null,
  ZLBZ CHAR(1) not null
)
-- Create/Recreate primary, unique and foreign key constraints
alter table INPUT_FWMJ
  add constraint 機構編號和部門編號 primary key (JGBH, BMBH)

--補錄表
-- Create table
create table INPUT_FWMJ_CHANGE
(
  TASKID     NUMBER not null,
  STATE      VARCHAR2(100),
  JGBHSOURCE CHAR(5),
  BMBHSOURCE CHAR(10),
  JGBH       CHAR(5) not null,
  JGMC       VARCHAR2(50) not null,
  BMBH       CHAR(10) not null,
  BMMC       VARCHAR2(50) not null,
  FWMJ       NUMBER(20,2) not null,
  ZLBZ       CHAR(1) not null
)

-- Create/Recreate primary, unique and foreign key constraints
alter table INPUT_FWMJ_CHANGE
  add constraint PK_INPUT_FWMJ_CHANGE primary key (JGBH, BMBH, TASKID)

              
--增加
                insert
          into input_fwmj(jgbh, jgmc, bmbh, bmmc, fwmj, zlbz)
                select jgbh, jgmc, bmbh, bmmc, fwmj, zlbz
                  from input_FWMJ_change
                 where state = 'Added'
                --刪除
                 delete input_fwmj
                 where jgbh || bmbh in
                       (select JGBHSOURCE || BMBHSOURCE
                          from input_FWMJ_change
                         where input_FWMJ_change.State = 'Deleted')
                --修改
                 update input_fwmj set
                 input_fwmj.fwmj =
                       (select input_FWMJ_change.fwmj
                          from input_FWMJ_change
                         where input_FWMJ_change.State = 'Updated'
                           and input_FWMJ_change.JGBHSOURCE =
                               input_fwmj.jgbh
                           and input_FWMJ_change.BMBHSOURCE =
                               input_fwmj.bmbh),
                 input_fwmj.zlbz =
                       (select input_FWMJ_change.zlbz
                          from input_FWMJ_change
                         where input_FWMJ_change.State = 'Updated'
                           and input_FWMJ_change.JGBHSOURCE =
                               input_fwmj.jgbh
                           and input_FWMJ_change.BMBHSOURCE =
                               input_fwmj.bmbh)
                 where exists((select 1
                                from input_FWMJ_change
                               where input_FWMJ_change.State = 'Updated'
                                 and input_FWMJ_change.JGBHSOURCE =
                                     input_fwmj.jgbh
                                 and input_FWMJ_change.BMBHSOURCE =
                                     input_fwmj.bmbh))