Oracle 12c - insert duplicates with sequence from another table

Question

I have the following insert statement which fails with ORA-00001 unique constraint violated (it's a PK violation). The PK is on c_id,f_id.

INSERT 
   INTO ab (c_id
          , d_amt
          , e_date
          , f_id)
   SELECT c_id
        , d_amt
        , e_date
        , (SELECT NVL(MAX(f_id) + 1,1) --this is causing the ORA-00001 error on duplicate c_id's
             FROM ab
            WHERE c_id = cx.c_id) f_id
     FROM xx cx

My objective is to insert data from xx into ab, if there are duplicate c_id values in xx, then the insert statement should increment the f_id value by 1 for every duplicate after the first one.

Answer 1

You could change your subquery to an inline view which you outer-join to, and then use an analytic function to to the incrementing:

INSERT 
   INTO ab (c_id
          , d_amt
          , e_date
          , f_id)
   SELECT cx.c_id
        , cx.d_amt
        , cx.e_date
        , NVL(ab2.f_id, 0) + ROW_NUMBER() OVER (PARTITION BY cx.c_id ORDER BY NULL)
     FROM xx cx
     LEFT JOIN (SELECT c_id, MAX(f_id) as f_id FROM ab GROUP BY c_id) ab2
     ON ab2.c_id = cx.c_id

db<>fiddle demo

The ORDER BY NULL is a bit ropey; if you care what the sequence is within the inserted rows you could order by one or more of the other column available from cx.