Oracle SQL - increment the sequence based on column-CodePudding

I have a table:

ITEM_EXPENSES having columns:

ITEM | SUPPLIER | SEQ_NO | COUNTRY
100      A10        1       UAE
100      A10        2       SAU

I have another table with the same structure ITEM_EXPENSES_DELTA

ITEM | SUPPLIER | SEQ_NO | VALUE
100      A10        1       USA
100      A10        2       THA

I want to insert ITEM_EXPENSES_DELTA into ITEM_EXPENSES but the unique key is on ITEM, SUPPLIER, SEQ_NO

I want to data to be like:

ITEM | SUPPLIER | SEQ_NO | VALUE
100      A10        1       UAE
100      A10        2       SAU
100      A10        3       USA
100      A10        4       THA

The seq_no should be the next number on what is already in ITEM_EXPENSES

what should be the insert

CodePudding user response：

Here's one option:

Sample data:

SQL> select * from item_expenses;

      ITEM SUP     SEQ_NO COU
---------- --- ---------- ---
       100 A10          1 UAE
       100 A10          2 SAU

SQL> select * from item_expenses_delta;

      ITEM SUP     SEQ_NO COU
---------- --- ---------- ---
       100 A10          1 USA
       100 A10          2 THA

Insert:

SQL> insert into item_expenses (item, supplier, seq_no, country)
  2  with ms as
  3    (select max(seq_no) maxseq from item_expenses)
  4  select d.item, d.supplier, s.maxseq   rownum, country
  5  from item_expenses_delta d cross join ms s;

2 rows created.

Result:

SQL> select * from item_expenses;

      ITEM SUP     SEQ_NO COU
---------- --- ---------- ---
       100 A10          1 UAE
       100 A10          2 SAU
       100 A10          3 USA
       100 A10          4 THA

SQL>