Merge SQL to read the same table for insert values issue

Question

I am writing a merge statement to insert data into the destination table.

Requirement: The TEXT_SEQ column must get sequence numbers based on the present values of the table based on GROUP_ID and ADD_DATE

This is what I have so far resulting in error

MERGE INTO destination a USING source b
ON( A.GROUP_ID = B.GROUP_ID AND A.ADD_DATE = B.ADD_DATE )
WHEN NOT MATCHED THEN
INSERT ( A.GROUP_ID ,A.ADD_DATE ,A.SOME_TEXT ,A.TEXT_SEQ )
VALUES ( B.GROUP_ID ,B.ADD_DATE ,B.SOME_TEXT ,
           (
                SELECT NVL( MAX( C.TEXT_SEQ ),0 ) + 1
                FROM DESTINATION C
                WHERE C.GROUP_ID = B.GROUP_ID
                AND C.ADD_DATE = B.ADD_DATE
            )
        )

@Boneist SQL Error: ORA-00904: "B"."ADD_DATE": invalid identifier — Varun Rao, May 05 '16 at 09:39
Which version are you using? That doesn't seem to error in 11.2.0.4. I'm not sure the logic makes sense though; if there is no match on group/date the current max for that group/date in the subquery will always get null? And if your source had multiple rows for the same group/date (with no match, with any sequence) the merge would give them all a value of 1, I think. Maybe you've obscured what you're doing too much? — Alex Poole, May 05 '16 at 10:03

score 0 · Answer 1 · edited May 23 '17 at 10:33

If I understand your requirement properly, you can do this with an insert instead of a merge. If you extend your subquery you can get all the source rows with a generated 'sequence' value based on data in the destination table. Starting with some dummy data:

CREATE TABLE DESTINATION (GROUP_ID NUMBER, ADD_DATE DATE, SOME_TEXT VARCHAR2(20), TEXT_SEQ NUMBER);

INSERT INTO DESTINATION VALUES (42, DATE '2016-05-01', 'Foo 1', 1);
INSERT INTO DESTINATION VALUES (42, DATE '2016-05-01', 'Foo 2', 2);
INSERT INTO DESTINATION VALUES (42, DATE '2016-05-01', 'Foo 3', 3);
INSERT INTO DESTINATION VALUES (42, DATE '2016-05-02', 'Bar 1', 1);

CREATE TABLE SOURCE (GROUP_ID NUMBER, ADD_DATE DATE, SOME_TEXT VARCHAR2(20));

INSERT INTO SOURCE VALUES (42, DATE '2016-05-01', 'New foo 1');
INSERT INTO SOURCE VALUES (42, DATE '2016-05-01', 'New foo 2');
INSERT INTO SOURCE VALUES (42, DATE '2016-05-01', 'New foo 3');
INSERT INTO SOURCE VALUES (42, DATE '2016-05-02', 'New bar 1');
INSERT INTO SOURCE VALUES (42, DATE '2016-05-03', 'New baz 1');
INSERT INTO SOURCE VALUES (43, DATE '2016-05-02', 'New qux 1');

And using the current maximum value plus the analytic row number of the source data for the group/date:

SELECT S.GROUP_ID, S.ADD_DATE, S.SOME_TEXT,
  NVL( MAX( D.TEXT_SEQ ), 0)
    + (ROW_NUMBER() OVER (PARTITION BY S.GROUP_ID, S.ADD_DATE ORDER BY 1)) AS TEXT_SEQ
FROM source s
LEFT JOIN destination d
ON D.GROUP_ID = S.GROUP_ID AND D.ADD_DATE = S.ADD_DATE
GROUP BY S.GROUP_ID, S.ADD_DATE, S.SOME_TEXT
/

  GROUP_ID ADD_DATE   SOME_TEXT              TEXT_SEQ
---------- ---------- -------------------- ----------
        42 2016-05-01 New foo 1                     4
        42 2016-05-01 New foo 2                     5
        42 2016-05-01 New foo 3                     6
        42 2016-05-02 New bar 1                     2
        42 2016-05-03 New baz 1                     1
        43 2016-05-02 New qux 1                     1

You can then use that for your insert:

INSERT INTO destination ( GROUP_ID ,ADD_DATE ,SOME_TEXT ,TEXT_SEQ )
SELECT ...

6 rows inserted.

SELECT GROUP_ID, ADD_DATE, TEXT_SEQ, SOME_TEXT
FROM DESTINATION
ORDER BY GROUP_ID, ADD_DATE, TEXT_SEQ;

  GROUP_ID ADD_DATE     TEXT_SEQ SOME_TEXT          
---------- ---------- ---------- --------------------
        42 2016-05-01          1 Foo 1               
        42 2016-05-01          2 Foo 2               
        42 2016-05-01          3 Foo 3               
        42 2016-05-01          4 New foo 1           
        42 2016-05-01          5 New foo 2           
        42 2016-05-01          6 New foo 3           
        42 2016-05-02          1 Bar 1               
        42 2016-05-02          2 New bar 1           
        42 2016-05-03          1 New baz 1           
        43 2016-05-02          1 New qux 1

You could also use the same query as the using clause of a merge, and add A.TEXT_SEQ = B.TEXT_SEQ to the ON clause; but as you will never have a match because of how the sequence is generated, you will always go into the WHEN NOT MATCHED branch, so I don't see you gain anything over a simple insert here.

(Of course, using max(id) + 1m isn't safe if you can have multiple sessions modifying the destination table at the same time, but you have that issue however you arrange the insert/merge).

Merge SQL to read the same table for insert values issue

1 Answers1