CODEWITHSUNDEEP
sql-updatesap-ase
tt0686
tt0686

Reputation: 1849

Commit 5000 rows each time

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

Upvotes: 1

Views: 2617

Answers (2)

Vince
Vince

Reputation: 734

If it is not possible to add an identity column in production and you have a column with dates, you could also use the date as an ID to update some periods of time:

SELECT @slicedStartDate = @startDate
SELECT @slicedEndDate = @startDate

WHILE (@slicedEndDate < @endDate)
BEGIN

  SELECT @slicedEndDate = dateAdd(hh, @timeSlice, @slicedStartDate)

  UPDATE xxxx WHERE date_column >= @slicedStartDate AND date_column < @slicedEndDate

  SELECT @slicedStartDate = @slicedEndDate

END

Upvotes: 0

Robert
Robert

Reputation: 25763

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.

Upvotes: 1

Related Questions