SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Rename Duplicate row value and make it composite primary key

prejibNov 11 2015 — edited Mar 28 2018

Hi,

Please help me to rename the value in column tag by suffixing 1,2,3..n to make a composite primary key (user,tag)

create table address(USER_ID

VARCHAR2(40 ),TAG

VARCHAR2(42 ),ADDRESS_ID

VARCHAR2(40 ));

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100018','AA','m101156');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100018','AA','m101608');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100018','AA','m101592');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100018','AA','m101593');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100023','BB','m101786');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100023','BB','m101720');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100023','BB','m101046');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100089','Hogar','m101160');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100156','CC','m101228');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100156','CC','m101104');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100156','CC','m101140');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100180','house','m101158');

Insert into ADDRESS (USER_ID,TAG,ADDRESS_ID) values ('m100181','Home','m101158');

SQL> select * from address order by USER_ID ;

USER_ID TAG ADDRESS_ID

---------------------------------------- ------------------------------------------ ----------------------------------------

m100018 AA m101156

m100018 AA m101608

m100018 AA m101592

m100018 AA m101593

m100023 BB m101786

m100023 BB m101720

m100023 BB m101046

m100089 Hogar m101160

m100156 CC m101228

m100156 CC m101104

m100156 CC m101140

m100180 house m101158

m100181 Home m101158

I need to replace the value in column TAG with 'tag||rownum' as in the NEW_TAG column . But the renaming should be from the rownum 1 onwards for each group. Please provide a update query for the same .

SQL> select user_id,tag,tag||rn New_Tag,rn from (select user_id,tag,(row_number() over(partition by user_id,tag order by rowid)-1) rn

from address ) a ; 2

USER_ID TAG NEW_TAG RN

---------------------------------------- ------------------------------------------ ------------------------------- ----------

m100018 AA AA -->don't suffix 0

m100018 AA AA1 1

m100018 AA AA2 2

m100018 AA AA3 3

m100023 BB BB0 0

m100023 BB BB1 1

m100023 BB BB2 2

m100089 Hogar Hogar-->don't suffix 0

m100156 CC CC-->don't suffix 0

m100156 CC CC1 1

m100156 CC CC2 2

m100180 house house0 0

m100181 Home Home 0

m100181 Home Home1 1

Thanks & Regards

Prejib

This post has been answered by BluShadow on Nov 13 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 11 2015

Added on Nov 11 2015

8 comments

898 views