oracle - How to update record which is duplicated PL/SQL in procedure -

oracle - How to update record which is duplicated PL/SQL in procedure -

i have table called sales_staff_08 , firstname, surname, username 3 cols.

username concatenated using firstname , surname.

because might duplicated. hence want avoid adding number.

current table

username ---------- johsmith1 tomnguye1 steredmo1 bobjohn1 carjones1 dancreig1 steredmo1 tomnguye1

i want update record duplicated, should shown this:

username ---------- johsmith1 tomnguye1 steredmo1 bobjohn1 carjones1 dancreig1 steredmo2 tomnguye2

my first effort update records

create or replace procedure proc_concate_names vc_username varchar(25); v_number number (2) := 1; cursor cur_concate_username select firstname, surname, username sales_staff_08; begin rec_cur_concate in cur_concate_username loop vc_username := rec_cur_concate.firstname || rec_cur_concate.surname || v_number; update sales_staff_08 ss set username = vc_username ss.username = rec_cur_concate.username; end loop; end proc_concate_names; /

you can 1 command:

merge sales_staff_08 d using (select firstname ||surname || row_number() on (partition username order firstname) un, rowid sales_staff_08 ) s on (d.rowid = s.rowid) when matched update set d.username = s.un;

here sqlfiddle demo

as @plouf stated can done update command:

update sales_staff_08 d set d.username = ( select united nations ( select firstname ||surname || row_number() on (partition firstname, surname order firstname) united nations sales_staff_08 ) s s.rowid = d.rowid )

here sqlfiddle

oracle plsql duplicates