CODEWITHSUNDEEP
oracle-databaseloopsplsql
Yuliya Sokhrannaya
Yuliya Sokhrannaya

Reputation: 83

Oracle: update non-unique field

I need to update a non-unique field. I have a table tbl:

create table tbl (A number(5));

Values in tbl: 1, 2, 2, 2 .. 2.

I need to replace all 2 with new non-unique values

New values: 1, 100, 101, 102, 103 .. I wrote: 

DECLARE
    sql_stmt VARCHAR2(500);
   cursor curs is 
        select A from tbl group by A having count(*)>1;
   l_row curs%ROWTYPE;
   i number(5);
   new_mail VARCHAR2(20);
BEGIN
  i:=100;
  open curs;
  loop
    fetch curs into l_row;
    exit when curs%notfound; 
    SQL_STMT := 'update tbl set a='||i||' where a='||l_row.A;
    i:=i+1;
    EXECUTE IMMEDIATE sql_stmt;
  end loop;
  close curs;
END;
/

But I got:

         A
----------
         1
       100
       ...
       100

What can be wrong? Why doesn't the loop work?

Upvotes: 0

Views: 347

Answers (2)

haki
haki

Reputation: 9759

what about 

update tbl
set a = 100 + rownum 
where a in (
    select a 
    from    tbl 
    group by a
    having count(*) > 1 )

the subquery finds duplicated A fields and the update gives them the unique identifier starting from 100. (you got other problems here like , what if id 100, 101.... already exists ).

first rule of PLSQL says that what ever you can do with SQL always do with SQL. writing straight up for loop cause allot of context switches between the sql and pl/sql engine. even if oracle automatically converts this to a bulk statement (10g<) it will still be faster with pure SQL.

Upvotes: 5

Alex Poole
Alex Poole

Reputation: 191305

Your cursor gets one row per unique value of A:

select A from tbl group by A having count(*)>1;

You need to get all the distinct rows that match those values. One way is to do this:

select a, r from (
    select a, rowid as r, count(*) over (partition by a) as c
    from tbl
) where c > 1;

... and then use the rowid values to do the update. I'm not sure why you're using dynamic SQL as it is not at all necessary, and you can simplify (IMO) the loop:

declare
    i number(5);
begin
    i:=100;
    for l_row in (
        select a, r from (
            select a, rowid as r, count(*) over (partition by a) as c
            from tbl
        ) where c > 1) loop
        update tbl set a=i where rowid = l_row.r;
        i:=i+1;
    end loop;
end;
/

I've kept this as PL/SQL to show what was wrong with what you were attempting, but @haki is quite correct, you should (and can) do this in plain SQL if at all possible. Even if you need it to be PL/SQL because you're doing other work in the loop (as the new_mail field might suggest) then you might be able to still do a single update within the procedure, rather than one update per iteration around the loop.

Upvotes: 1

Related Questions