Commit 5000 rows each time

Question

Good afteroon in my timezone.

I want to update a table , the RDBMS used is the the Sybase ASE 15. Because the table contains almost 1 million rows , and i have to run this update in Production environment, i want to update and commit every 10000 rows.I do not have experience in Sysbase. Can anyone help me, if possible putting some code example

Thanks in advance Best Regards

Answer 1

Try something like this:

 -- declaration
  declare @counter int,
          @MaxId int,
          @Rows int

  select @counter=0 -- start position
  select @Rows=10000  -- how many rows do you want to update in one time

  select @MaxId = count(*) 
  from   tab
  -- updating in loop
  while @counter<@MaxId+@Rows
  begin

    update tab
    set    col1 = 'val'     
    where  id between @counter and @counter+@Rows-1 

    select @counter=@counter+@Rows

  end
  go

EDIT:

If table tab hasn't got Unique or PK column than you could add an identity column as below

alter table tab
add id numeric(10,0) identity

than you can run above solution.