Sync tables: generate MERGE using Unique constraint

In my post “Compare and sync tables: Generating the code“, I use the primary key constraint on the target table. A reader called “Bal” asked how to use a unique constraint instead.

(Here is a list of my posts about comparing and synchronizing tables.)

Test data

I’m going to create a target table with a unique constraint, but without any NOT NULL constraints on the columns.

drop table t_target purge;
create table t_target as
with col1 as (
  select 1 uk1 from dual
  union all
  select null from dual
)
, col2 as (
  select 2 uk2 from dual
  union all
  select null from dual
)
select uk1, uk2, 'Old value '|| rownum val from col1, col2;
alter table t_target add constraint t_target_uk unique(uk1,uk2);

drop table t_source purge;
create table t_source as
select uk1, uk2, replace(val,'Old','New') val from t_target;
UK1 UK2 VAL
1 2 New value 1
1 New value 2
2 New value 3
New value 4

Changes to the SQL template

I am going to compare columns UK1 and UK2, which may contain NULL values. I like to use DECODE for that. In this case, I want to generate

on (0 = ALL(decode(o.UK1,n.UK1,0,1),decode(o.UK2,n.UK2,0,1)))

So in my revised template, that line will read

on (0 = ALL(#ON_COLS#))

Changes to the code generator

Here is the new code generator. I will highlight the changed lines

VARIABLE P_OLDOWNER varchar2(30)
VARIABLE P_OLDTABLE varchar2(30)
VARIABLE P_NEWSOURCE varchar2(256)
EXEC :P_OLDTABLE := 'T_TARGET';
EXEC :P_NEWSOURCE := 'T_SOURCE';

with INPUT as (
  select UPPER(NVL(:P_OLDOWNER, user)) OLD_OWNER,
  UPPER(:P_OLDTABLE) OLD_TABLE_NAME,
  :P_NEWSOURCE NEW_SOURCE,
  UPPER(NVL2(:P_OLDOWNER, :P_OLDOWNER || '.' || :P_OLDTABLE, :P_OLDTABLE)) OLD_TABLE
  from DUAL
)
, TAB_COLS as (
  select COLUMN_NAME, INTERNAL_COLUMN_ID COLUMN_ID
  from ALL_TAB_COLS, INPUT
  where (OWNER, TABLE_NAME) = ((OLD_OWNER, OLD_TABLE_NAME))
)
, KEY_COLS as (
  select COLUMN_NAME, POSITION
  from ALL_CONS_COLUMNS, INPUT
  where (OWNER, CONSTRAINT_NAME) = (
    select OWNER, CONSTRAINT_NAME from ALL_CONSTRAINTS
    where (OWNER, TABLE_NAME, CONSTRAINT_TYPE) = ((OLD_OWNER, OLD_TABLE_NAME, 'U'))
  )
)
, COL_LIST as (
  select LISTAGG(COLUMN_NAME,',') within group(order by COLUMN_ID) ALL_COLS,
  LISTAGG('n.' || COLUMN_NAME,',') within group(order by COLUMN_ID) INSERT_COLS
  from TAB_COLS
)
, PK_LIST as (
  select LISTAGG(COLUMN_NAME,',') within group(order by POSITION) PK_COLS,
  LISTAGG('decode(o.'||COLUMN_NAME||',n.'||COLUMN_NAME||',0,1)',',')
    within group(order by POSITION) ON_COLS
  from KEY_COLS
)
, SET_LIST as (
  select LISTAGG(COLUMN_NAME || '=n.'||COLUMN_NAME,',')
    within group(order by COLUMN_ID) SET_COLS
  from TAB_COLS
  where COLUMN_NAME not in (select COLUMN_NAME from KEY_COLS)
)
select MULTI_REPLACE.TO_VARC(
'merge into #OLD_TABLE# O
using (
  select * from (
    select #ALL_COLS#,
    COUNT(*) over(partition by #PK_COLS#)
      - SUM(Z##_CNT) Z##IUD_FLAG
    from (
      select #ALL_COLS#,
        -1 Z##_CNT
      from #OLD_TABLE# O
      union all
      select #ALL_COLS#,
        1 Z##_CNT
      from #NEW_SOURCE# N
    )
    group by #ALL_COLS#
    having SUM(Z##_CNT) != 0
  )
  where Z##IUD_FLAG < 3
) N
on (0 = ALL(#ON_COLS#))
when matched then update
  set #SET_COLS#
  delete where N.Z##IUD_FLAG = 2
when not matched then insert
  (#ALL_COLS#)
  values(#INSERT_COLS#)',
SYS.ODCIVARCHAR2LIST('#OLD_TABLE#','#ALL_COLS#','#PK_COLS#',
  '#NEW_SOURCE#','#ON_COLS#','#SET_COLS#','#INSERT_COLS#'),
SYS.ODCIVARCHAR2LIST(  OLD_TABLE,    ALL_COLS,    PK_COLS,
    NEW_SOURCE,    ON_COLS,    SET_COLS,    INSERT_COLS)
) SQL_TEXT
from INPUT, COL_LIST, PK_LIST, SET_LIST;

The generated code is:

merge into T_TARGET O
using (
  select * from (
    select UK1,UK2,VAL,
    COUNT(*) over(partition by UK1,UK2)
      - SUM(Z##_CNT) Z##IUD_FLAG
    from (
      select UK1,UK2,VAL,
        -1 Z##_CNT
      from T_TARGET O
      union all
      select UK1,UK2,VAL,
        1 Z##_CNT
      from T_SOURCE N
    )
    group by UK1,UK2,VAL
    having SUM(Z##_CNT) != 0
  )
  where Z##IUD_FLAG < 3
) N
on (0 = ALL(decode(o.UK1,n.UK1,0,1),decode(o.UK2,n.UK2,0,1)))
when matched then update
  set VAL=n.VAL
  delete where N.Z##IUD_FLAG = 2
when not matched then insert
  (UK1,UK2,VAL)
  values(n.UK1,n.UK2,n.VAL);

Limitations

I have not tested this with tables that have more than one unique constraint.

Be warned that a unique constraint does not prevent duplicate lines with all NULL values. If the source can have more than one row with all NULL values in UK1 and UK2, this solution is not appropriate.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s