Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Query help to clone with certain condition

Ricky007May 16 2024

Please help to clone entries from the userinfo table to the userinfo_clone table with a condition that
if the userid already exists in userinfo_clone, then create a new entry with the same cloneid

create table userinfo
(
userid number,
usergroup number,
usercode varchar2(20)
);


insert into userinfo values(2001,2,'HK');
insert into userinfo values(2002,2,'HK');
insert into userinfo values(2003,2,'HK');
insert into userinfo values(1559,2,'UK');
insert into userinfo values(1654,2,'UK');
insert into userinfo values(1765,2,'UK');

create table userinfo_clone
(
cloneid number,
userid number,
usercode varchar2(20)
);

ALTER table userinfo_clone add primary key (userid);

insert into userinfo_clone values(50,1559,'UK');
insert into userinfo_clone values(76,1654,'UK');
insert into userinfo_clone values(58,1765,'UK');
commit;

I want to clone HK in userinfo_clone table

insert into userinfo_clone (cloneid,userid,usercode) 
select userid,usergroup,usercode from userinfo where usergroup=2;

ORA-00001: unique constraint violated.

insert into userinfo_clone (cloneid,userid,usercode) 
select uc.cloneid,uc.userid,uc.usercode from userinfo ui ,
userinfo_clone uc
where uc.userid=ui.userid
and ui.usergroup=2;

ORA-00001: unique constraint violated.

if the userid matches then add a new entry for HK usercode in the userinfo_clone by having the same clone_id

Expected result after running

select * from userinfo_clone

50 1559  UK
76 1654  UK
58 1765  UK
50 2001  HK
76 2002  HK
58 2003  HK

Please help
Comments
Post Details
Added on May 16 2024
5 comments
108 views