Warm tip: This article is reproduced from stackoverflow.com, please click
sql plsql

Updating database records in a loop?

发布于 2020-03-28 23:13:58
declare
begin
  for i in (select * from emp)
  loop
    if i.sal=1300 then
      update emp
      set sal=13000;
    end if;
  end loop;
end;

This code is updating all the records with salary 13000.
Instead i want to update records having salary 1300 to the value 13000.
Can you tell where I made a mistake?
I am accesing records using implicit cursor..
for every record i am checking the sal value of that record..
if salary value in a particular record is 1500 i want to update it to 15000..

Questioner
musicking123
Viewed
42
Erich Kitzmueller 2009-02-19 20:41

Whenever you can do the update with one single statement, you should do that instead of using a loop. You'll get a very huge performance gain that way; or, the other way round, updates in a loop cost you a lot of performance.

If you really really have to use a loop, of course you need a where condition to make sure you are only updating the record you really want to update. A possible way that always works (even if there is no unique key available) is to use the rowid pseudocolumn:

begin
  for i in (select rowid, emp.* from emp)
  loop
    if i.sal=1300 then
      update emp
      set sal=13000
      where rowid=i.rowid;
    end if;
  end loop;
end;

Another possibility would be using an explicit cursor and the "update ... where current of cursorname" syntax.