Oracle SQL - increment the sequence based on column

Question

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

Littlefoot · Accepted Answer · 2022-04-15 10:06:25Z

1

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>

answered Apr 15, 2022 at 10:06

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL - increment the sequence based on column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related