CODEWITHSUNDEEP
sqloracle-database
Simon
Simon

Reputation: 23

sql update query

I want to strip off all _ prefixes in the name column, but the result may cause a conflict. So if the result duplicates with existing ones, I want to suffix a _ to it until there's no duplication.

In the below example case, _test should be renamed to test___.

create table A  
(  
  name  VARCHAR2(20) unique,  
  id int  
);  
insert into a (name, id) values ('_test', 1);  
insert into a (name, id) values ('test', 2);  
insert into a (name, id) values ('test_', 3);  
insert into a (name, id) values ('test__', 4);

Upvotes: 2

Views: 116

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

Try this:

merge into A
using (with aa as (select id, trim('_' from name) name from A)
     select rpad(name,
               length(name) - 1 + row_number()
               over(partition by name order by id),
               '_') name2,
          id
     from AA) s
on (s.id = a.id)
when matched then
  update set a.name = s.name2

Upvotes: 5

Related Questions