i have a PL/SQL procedure using MERGE :
MERGE INTO
table_dest d
USING
(SELECT * FROM my_Table) s
ON
(s.id = d.id)
when matched then UPDATE set d.col1 = s.col1
when not matched then INSERT (id, col1) values (s.id, s.col1);
now lets say the query s
returns mutiple rows with same id wich will returns an ORA-00001: unique constrain error
what i want to do is to send the duplicated columns to another table my_Table_recyledbin to get a successful INSERT, can i use EXCEPTION WHEN DUP_VAL_ON_INDEX
? if yes how to use it with the MERGE statement?
Thanks in advance
Why not handle the archiving of duplicate rows to the recycle bin table in a separate statement?
Firstly, do your merge (aggregating the duplicate rows to avoid the unique constraint error). I've assumed a MAX
aggregate function on col1, but you can use whatever suits your needs -- you have not specified how to decide which row to use when there are duplicates.
MERGE INTO
table_dest d
USING
(SELECT a.id, MAX(a.col1) as col1
FROM my_Table a
GROUP BY a.id) s
ON
(s.id = d.id)
WHEN MATCHED THEN UPDATE SET d.col1 = s.col1
WHEN NOT MATCHED THEN INSERT (id, col1) VALUES (s.id, s.col1);
Then, deal with the duplicate rows. I'm assuming that your recycle bin table does allow duplicate ids to be inserted:
INSERT INTO my_Table_recyledbin r (id, col1)
SELECT s.id, s.col1
FROM my_Table s
WHERE EXISTS (SELECT 1
FROM my_Table t
WHERE t.id = s.id
AND t.ROWID != s.ROWID)
Hopefully, that should fulfil your needs.