CODEWITHSUNDEEP
sqloracle-databasemerge
Nico Richter
Nico Richter

Reputation: 379

Sync two tables with single SQL

I need to sync two table in Oracle. I used MERGE to do this job, but I need help to get a working SQL to do this.

My target table has a PK and some other columns. Some of these columns has not null constraint.

My source table has a different layout and data then my target table, so I need to query my source table and convert data to target layout.

My actual code is (simplified):

MERGE INTO TARGET t USING(
    WITH SRC AS ( --do the transformation
        SELECT ID, DECODE(VAL,'THIS','THAT','OTHER') VAL1, REGEXP_SUBSTR(VAL,'\d+') VAL2 FROM SOURCE
    )
    SELECT t.ROWID ROW_ID, s.* FROM SRC s
    FULL OUTER JOIN TARGET t ON s.ID=t.ID
) s ON (t.ROWID=s.ROW_ID)
WHEN MATCHED THEN UPDATE SET t.VAL1=s.VAL1 AND t.VAL2=s.VAL2
DELETE WHERE s.ID IS NULL
WHEN NOT MATCHED THEN INSERT(ID, VAL1, VAL2) VALUES (s.ID, s.VAL1, s.VAL2);

The problem is that these rows, that match the DELETE condition, throws ORA-01407: cannot update (string) to NULL. It seems, that Oracle first tries to update and do the delete later. This causes the ERROR.

The MERGE keyword is really horrible for sync Tables with deletion, but I'd like to use a single Query, because my transformation SQL is really heavy.

Is there any alternative to MERGE or any suggestion what to do, to get this working?

Thanks

Thats my solution. Maybe this can help someone.

MERGE INTO TARGET t USING(
    WITH SRC AS ( --do the transformation
        SELECT ID, DECODE(VAL,'THIS','THAT','OTHER') VAL1, REGEXP_SUBSTR(VAL,'\d+') VAL2 FROM SOURCE
    )
    SELECT t.ROWID ROW_ID, NVL2(s.ID,null,1) delFlag, s.* 
    FROM SRC s
    FULL OUTER JOIN TARGET t ON s.ID=t.ID
) s ON (t.ROWID=s.ROW_ID)
WHEN MATCHED THEN UPDATE SET t.VAL1=NVL2(s.delFlag,t.VAL1,s.VAL1) AND t.VAL2=NVL2(s.delFlag,t.VAL1,s.VAL2)
DELETE WHERE s.delFlag IS NOT NULL
WHEN NOT MATCHED THEN INSERT(ID, VAL1, VAL2) VALUES (s.ID, s.VAL1, s.VAL2);

But it's really strange that rows that will be deleted must pass constraint checks.

Upvotes: 2

Views: 1889

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

As weird as it seems, rather than either updating or deleting a row, Oracle's MERGE only deletes rows that it already updated. Doesn't seem to make sense, but that's the way it is. Please see Boneist's answer for a solution.

Update: I've looked this up in some standard SQL drafts. From what I gather, MERGE in standard SQL 2003 only supported insert and delete, and both WHEN MATCHED THEN and WHEN NOT MATCHED THEN were not allowed to appear more than once.

Oracle obviously wanted to provide a delete option and decided for the weird approach to extend the update clause and apply deletes on updated rows only.

In a newer draft (supposedly the current one, found here: https://www.wiscorp.com/SQLStandards.html) a delete option was added, the WHEN clauses extended to WHEN MATCHED [ AND <search condition> ] THEN and WHEN NOT MATCHED [ AND <search condition> ] THEN, and these clauses can appear more than once. This looks way better. Let's hope Oracle adopts this new syntax some time soon :-)

The problem is that these rows, that match the DELETE condition, throws ORA-01407: cannot update (string) to NULL. It seems, that Oracle first tries to update and do the delete later. This causes the ERROR.

Then give the UPDATE part a condition too:

WHEN MATCHED THEN
  UPDATE SET t.VAL1=s.VAL1 AND t.VAL2=s.VAL2 WHERE s.ID IS NOT NULL
  DELETE WHERE s.ID IS NULL

Upvotes: 0

Boneist
Boneist

Reputation: 23578

In order to delete rows as part of the merge statement, you need to first update the row.

Therefore, you need to take account of null values in the update statement (either directly in the set clause, or in the source query), e.g.:

MERGE INTO target t
  USING (WITH src AS ( --do the transformation
                      SELECT id,
                             DECODE(val, 'THIS', 'THAT', 'OTHER') val1,
                             regexp_substr(val, '\d+') val2
                      FROM   SOURCE)
         SELECT t1.rowid row_id,
                s1.id,
                NVL(s1.val1, t1.val1) val1,
                NVL(s1.val2, t1.val2) val2
         FROM   src s1
         FULL   OUTER JOIN target t1
         ON     s1.id = t1.id) s ON (t.rowid = s.row_id)
WHEN MATCHED THEN
  UPDATE SET t.val1 = s.val1 AND t.val2 = s.val2
  DELETE WHERE s.id IS NULL
WHEN NOT MATCHED THEN
  INSERT (id, val1, val2)
  VALUES (s.id, s.val1, s.val2);

N.B. I am assuming that the val1 and val2 in the source and target tables are not nullable. Amend as appropriate for your tables.

Upvotes: 3

Related Questions