I currently wish to write a query to duplicate data in database while modifying one column.
First of all, my database structure:
table code

CURCD                                     NOT NULL VARCHAR2(3)
CODE                                      NOT NULL VARCHAR2(2)
ITEM                                               NUMBER(1)
DSCPT                                              VARCHAR2(20)

The unique idx is the curcd,code,item.

What I wish to achieve is duplicate data of code '80' with a set of code = '82'
So, my query will be insert into code (CURCD,CODE,ITEM,DSCPT) select CURCD,'82',ITEM,DSCPT from code where code = '80';
this is working. Then I planned to create for code = '83', but due to there is already having existing data in the table for code 83, can anyone giving advises on how to modify the script? The existing data is having curcd = 'MYR' and item is in (3,5)

I had tried the not exist without success.

@rch1231 it didn't work though. But I managed to done it by using 2 query instead of one query:

insert into code (CURCD,CODE,ITEM,DSCPT) 
select CURCD,'83',ITEM,DSCPT from code where code = '80' and CURCD != 'MYR';

insert into code (CURCD,CODE,ITEM,DSCPT) 
select CURCD,'83',ITEM,DSCPT from code where code = '80' and CURCD = 'MYR' and ITEM not in (3,5);
commit;

`

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.