Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Inserting duplicate entries into the target table using MERGE

This is the extended version of the question : When I tried adding one additional column i.e jira_key it is loading duplicate entires.
https://community.oracle.com/tech/developers/discussion/comment/16839034#Comment_16839034
CREATE TABLE test_tab1(seq_id NUMBER(10), e_id NUMBER(10), jira_key varchar2(20), stage_code NUMBER(10)); INSERT INTO test_tab1(1,11,'A',2); INSERT INTO test_tab1(2,12,'B',3); CREATE SEQUENCE test_tab2_sq; CREATE TABLE test_tab2(seq_id NUMBER(10), e_id NUMBER(10), jira_key varchar2(20), stage_code NUMBER(10), start_date DATE, end_date DATE);
I need to load the data into the table test_tab2 based on the stage_code column of test_tab1.
Explanation:
If stage_code is 3 in test_tab1 table then there would be 4 entries in the table test_tab2 starting from 0 to 3 and start_date and end_date should be SYSDATE for stage_code < original stage_code and if stage_code = original code then end_date will be NULL. Below expected output.
+--------+------+----------+-------------+-------------+----------+ | SEQ_ID | E_ID | JIRA_KEY | STAGE_CODE | START_DATE | END_DATE | +--------+------+----------+-------------+-------------+----------+ | 1 | 11 | A | 0 | 21-06-22 | 21-06-22 | | 2 | 11 | A | 1 | 21-06-22 | 21-06-22 | | 3 | 11 | A | 2 | 21-06-22 | NULL | | 4 | 12 | B | 0 | 21-06-22 | 21-06-22 | | 5 | 12 | B | 1 | 21-06-22 | 21-06-22 | | 6 | 12 | B | 2 | 21-06-22 | 21-06-22 | | 7 | 12 | B | 3 | 21-06-22 | NULL | +--------+------+----------+-------------+-------------+----------+
Likewise if any stage_code got decreased then we need to make start_date and end_date entries as NULL for those stage_code.
But the MERGE is inserting duplicate records even that e_id is loaded into the target table it is again loading that data.
Code:
MERGE INTO transact_tab dst USING ( WITH got_new_code AS ( SELECT m.e_id,m.jira_key , m.code AS new_code , c.code FROM main_tab m CROSS APPLY ( SELECT LEVEL - 1 AS code -- subtracted 1 from level FROM dual CONNECT BY LEVEL <= m.code + 1 --Added + 1 ) c ) SELECT * FROM got_new_code n FULL JOIN transact_tab t USING (e_id,jira_key,code) ) src ON ( dst.e_id = src.e_id AND dst.code = src.code ) WHEN MATCHED THEN UPDATE SET dst.start_date = CASE WHEN dst.code <= src.new_code THEN dst.start_date ELSE NULL -- optional (NULL is default) END , dst.end_date = CASE WHEN dst.code < src.new_code THEN NVL (dst.end_date, SYSDATE) ELSE NULL -- optional (NULL is default) W END WHERE LNNVL (dst.code < src.new_code) OR dst.end_date IS NULL WHEN NOT MATCHED THEN INSERT (dst.e_id, dst.code, dst.start_date, dst.end_date,dst.jira_key) VALUES (src.e_id, src.code, SYSDATE, CASE WHEN src.code < src.new_code THEN SYSDATE ELSE NULL -- optional (NULL is default) END,src.jira_key ) ;
Best Answer
-
Hi, @Albert Chao
INSERT INTO test_tab1(1,11,'A',2);
INSERT INTO test_tab1(1,12,'B',3);
Would you like to get answers that work? Then make sure the statements you post to create the sample data work, too. Test (and, if necessary, fix) your statements before you post them.
Did you want seq_id = 1 on both rows in test_tab1?
Below expected output.
+--------+------+-------------+-------------+----------+ | SEQ_ID | E_ID | STAGE_CODE | START_DATE | END_DATE | +--------+------+-------------+-------------+----------+ | 1 | 11 | 0 | 21-06-22 | 21-06-22 | | 2 | 11 | 1 | 21-06-22 | 21-06-22 | | 3 | 11 | 2 | 21-06-22 | NULL | | 4 | 12 | 0 | 21-06-22 | 21-06-22 | | 5 | 12 | 1 | 21-06-22 | 21-06-22 | | 6 | 12 | 2 | 21-06-22 | 21-06-22 | | 7 | 12 | 3 | 21-06-22 | NULL | +--------+------+-------------+-------------+----------+
Don't you want jira_key in the results?
Answers
-
Hi, @Albert Chao
INSERT INTO test_tab1(1,11,'A',2);
INSERT INTO test_tab1(1,12,'B',3);
Would you like to get answers that work? Then make sure the statements you post to create the sample data work, too. Test (and, if necessary, fix) your statements before you post them.
Did you want seq_id = 1 on both rows in test_tab1?
Below expected output.
+--------+------+-------------+-------------+----------+ | SEQ_ID | E_ID | STAGE_CODE | START_DATE | END_DATE | +--------+------+-------------+-------------+----------+ | 1 | 11 | 0 | 21-06-22 | 21-06-22 | | 2 | 11 | 1 | 21-06-22 | 21-06-22 | | 3 | 11 | 2 | 21-06-22 | NULL | | 4 | 12 | 0 | 21-06-22 | 21-06-22 | | 5 | 12 | 1 | 21-06-22 | 21-06-22 | | 6 | 12 | 2 | 21-06-22 | 21-06-22 | | 7 | 12 | 3 | 21-06-22 | NULL | +--------+------+-------------+-------------+----------+
Don't you want jira_key in the results?
-
@Frank Kulash Very sorry for the incorrect sample data. I have edited the original question and the logic would be same like you did in previous question but when I am giving jira_key it is inserting duplicate entries
-
Hi, @Albert Chao
I have edited the original question
Please don't ever change your messages after you post them: it makes the thread hard to read and your changes easy to miss. Post all corrections and additions in a new message at the end of the thread, I can't see what you changed, but the INSERT statements still don't work
If you don't want jira_key in the results, why can't you use the solution from your earlier question?
-
@Frank Kulash I am able to handle this. Thanks for your help :)
-
HI,Albert Chao
But the MERGE is inserting duplicate records even that e_id is loaded into the target table it is again loading that data.
NOT MATTERED, so it (script) adds records
with /* test_tab1(seq_id,e_id,jira_key,stage_code) as ( select 1,11,'A',3 from dual union all select 2,12,'B',2 from dual ) ,test_tab2(seq_id,e_id,jira_key,stage_code,start_date,end_date) as ( select 1,11,'A',0,to_date('21-06-22','dd.mm.rr'),to_date('21-06-22','dd.mm.rr') from dual union all select 2,11,'A',1,to_date('21-06-22','dd.mm.rr'),to_date('21-06-22','dd.mm.rr') from dual union all select 3,11,'A',2,to_date('21-06-22','dd.mm.rr'),to_date('','dd.mm.rr') from dual union all -- select 4,12,'B',0,to_date('21-06-22','dd.mm.rr'),to_date('21-06-22','dd.mm.rr') from dual union all select 5,12,'B',1,to_date('21-06-22','dd.mm.rr'),to_date('21-06-22','dd.mm.rr') from dual union all select 6,12,'B',2,to_date('21-06-22','dd.mm.rr'),to_date('21-06-22','dd.mm.rr') from dual union all select 7,12,'B',3,to_date('21-06-22','dd.mm.rr'),to_date('','dd.mm.rr') from dual ) , */ got_new_code AS ( SELECT m.e_id,m.jira_key , m.stage_code , c.code FROM test_tab1 m CROSS APPLY ( SELECT LEVEL - 1 AS code FROM dual CONNECT BY LEVEL <= m.stage_code + 1 ) c ) ,src as ( SELECT * FROM got_new_code n FULL JOIN test_tab2 t USING (e_id,jira_key,stage_code) --src ) --select * from src select case when (dst.e_id = src.e_id AND dst.stage_code = src.code AND dst.jira_key = src.jira_key) then 'MATCHED' else 'NOT' end merge ,src.* from src left join test_tab2 dst ON (dst.e_id = src.e_id AND dst.stage_code = src.code AND dst.jira_key = src.jira_key) order by 1 MERGE E_ID JIRA_KEY STAGE_CODE CODE SEQ_ID START_DATE END_DATE MATCHED 11 A 2 0 3 06/21/2022 - MATCHED 11 A 2 1 3 06/21/2022 - MATCHED 11 A 2 2 3 06/21/2022 - MATCHED 12 B 3 2 5 06/21/2022 - MATCHED 12 B 3 3 5 06/21/2022 - MATCHED 12 B 3 0 5 06/21/2022 - MATCHED 12 B 3 1 5 06/21/2022 - NOT 11 A 0 - 1 06/21/2022 06/21/2022 NOT 11 A 1 - 2 06/21/2022 06/21/2022 NOT 12 B 2 - 4 06/21/2022 06/21/2022 NOT 12 B 0 - 6 06/21/2022 06/21/2022 NOT 12 B 1 - 7 06/21/2022 06/21/2022 12 rows returned in 0.04 seconds Download
-
@Stax Yes I have edited my answer which is working as expected and not giving me duplicate entries