CODEWITHSUNDEEP
oracle-databasemergeinsert
Jaikumar
Jaikumar

Reputation: 1

ORACLE : How to use manual ID generated using MAX ID with Oracle Merge statement insert query

I have 2 tables like below,

Master_Data_1

Id     Name
1      AAA 
2      BBB
3      DDD
4      CCC

Master_Data_2

Id     Name     Upt_Flg
1      DDD      False
2      EEE      False
3      FFF      False

both tables have same ID but different Name, and ID field is neither auto incremented nor sequence. But ID is a not null primary key column which will be generated based on MAX ID logic.

In this case i am trying to merge Master_Data_1 table with Master_Data_2 table using oracle merge statement.

So i am having issue in WHEN NOT MATCHED THEN case, i am trying to insert not matched records in to Master_data_2 table from master_data_1 table. In this scenario ID in Master_Data_2 table will not be auto incremented, and also i cannot use the source table ID here because it will lead to unique constraint exception.

Note : I don't want to alter a table to make ID auto increment or adding sequence for ID.

Expected Output in Master_Data_2 table using merge query:

Id     Name     Upt_Flg
1      DDD      TRUE
2      EEE      False
3      FFF      False
4      AAA      False
5      BBB      False
6      CCC      False

is there any other way to achieve this?

Upvotes: 0

Views: 1027

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I guess ROW_NUMBER analytic function might help you here.

MERGE INTO Master_Data_2 md2
USING (SELECT (SELECT MAX(id) FROM Master_Data_2) + ROW_NUMBER() OVER(ORDER BY id) new_id
             ,name
       FROM Master_Data_1
      ) md1
ON (md2.name = md1.name)
WHEN MATCHED THEN UPDATE
                  SET Upt_flg = 'TRUE'
WHEN NOT MATCHED THEN
INSERT (md2.id, md2.name)
VALUES (Md1.new_id, md1.name);

Here is the fiddle

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can simply achieve it using INSERT and MINUS operator as following:

insert into master_data_2
select 
(select max(id) from master_data_2) + row_number() over (order by 1),
name 
from
(select name from master_data_1
minus
select name from master_data_2)

db<>fiddle demo

Cheers!!

Upvotes: 0

Related Questions