Updating database records in a loop?

Question

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..

Answer 1

delete that code and just use:

update emp set sal = 13000 where sal = 1300

Answer 2

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.

Answer 3

You need to put a constraint on your update statement.

What you have at the moment will loop through the results rows, and if it finds a row with salary equal to 1300, if then executest he following SQL:

update emp 
set sal=13000;

Without the contraint this updates every row.

Answer 4

This code is updating all the records with salary 13000. Instead i want to update records having salary 1300 to the value 13000.

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..

So what exactly do you want?

You want to update only 1,500 salary, you issue:

UPDATE emp
SET sal = 15000
WHERE sal = 1500;

You want to increase all salary ten times, you issue:

UPDATE emp
SET sal = sal * 10;

Answer 5

While some of these solutions are workable its not a one size fits all solution. I ran into a scenario where we had to set an xml/nvarchar(max) field to null on a table that has +50 M records. This is an excerpt of the code

begin

declare @rows int,
        @y    int = 2020,
        @m    int = 1;

set @rows = 1;

while (@rows > 0)
begin

update top (500) cr
   set [xml] = null
 from [dbo].[customer] cr with(index(ix_customer_reportdt)) 
 where  year([reportdt]) = @y
   and month([reportdt]) = @m
   and [xml] is not null;

  set @rows = @@rowcount;

end

end

Answer 6

here's a quick solution that helps in removing the spaces (trimming) of a column data based on created date:

UPDATE table_Name SET column_name = LTRIM(RTRIM(column_name)) 
WHERE EXTEND(dateTime_column, YEAR TO DAY)='2020-01-31' ;