updating a specific field in a table x rows at a time

[collierd]

Hello

I am attempting to update a specific table 50000 - 100000 records at a time as the table contains about 4 million records. Currently doing the table in one swoop causes the command to fail with 'run out of memory' type error messages due to tempdb increasing to over 10 gb

I have had to split it into a number of runs using the following script:

begin transaction

update holdslines
  set hsluom = uom.hsluom
from holdslines hsl, sholdslinesuom uom
where hsl.holdslinesid = 'S' + uom.holdslinesid
and hsl.holdslinesid like '%LW%'

commit

where "and hsl.holdslinesid like '%LW%'" is changed to reflect different order number structures, ie. LA, LB, LC ... LW etc

This works but I need to create a script for each and if a new order sctructure comes along I will have to change the code to reflect this

I have tried the following for 50000 rows at a time:

--Set rowcount to 50000 to limit number of updates
--performed in each batch to 50K rows.
Set rowcount 50000

--Declare variable for row count
Declare @rc int
Set @rc=1

While @rc>0
 Begin

  Begin Transaction

  Update holdslines With (tablockx, holdlock)
    Set hsluom = uom.hsluom
  From holdslines hsl, sholdslinesuom uom
  --Add criteria to avoid updating rows that
  --were updated in previous pass
  where hsl.holdslinesid = 'S' + uom.holdslinesid
  and hsl.hsluom is null
 -- and hsl.hsluom <> uom.hsluom

  --Get number of rows updated
  --Process will continue until zero
  Select @rc=@@rowcount

  --Commit the transaction
  Commit

 End

This is run in the first instance where initially all hsluom's are null
It executes for a while but doesn't do anything to the table when I re-select showing hsluom (i.e. all are still null)

Does anybody know where this is going wrong?
Also does anybody know what to use if there is something in that field, i.e. if I want to amend that field for all records and the field is not null ? (I think it should just be hsl.hsluom <> uom.hsluom)

Thanks

Damian.

 

[Guest_imported]

Hi.

Can you add an extra row to the table (autonumber). Then add to the end of the query :

where autonumberfield <= 50000

then, the next time you execute it, use range :

where autonumberfield > 50000 and autonumberfield <=100000.

Hope this is the answer to your question.

 

[r937]

you might have better luck asking this question in the Microsoft SQL/Server forum -- forum183

rudy

http://rudy.ca/