Forum Stats

  • 3,826,359 Users
  • 2,260,635 Discussions
  • 7,896,916 Comments

Discussions

Categories

Inserting duplicate entries into the target table using MERGE

Albert Chao
Albert Chao Member Posts: 193 Green Ribbon
edited Jun 23, 2022 1:34PM in SQL & PL/SQL

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
      )
;


Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond
    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?

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond
    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?

    · Share on TwitterShare on Facebook
  • Albert Chao
    Albert Chao Member Posts: 193 Green Ribbon

    @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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond

    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?

    · Share on TwitterShare on Facebook
  • Albert Chao
    Albert Chao Member Posts: 193 Green Ribbon

    @Frank Kulash I am able to handle this. Thanks for your help :)

    · Share on TwitterShare on Facebook
  • Stax
    Stax Member Posts: 41 Red Ribbon

    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


    · Share on TwitterShare on Facebook
  • Albert Chao
    Albert Chao Member Posts: 193 Green Ribbon

    @Stax Yes I have edited my answer which is working as expected and not giving me duplicate entries

    · Share on TwitterShare on Facebook