October 3, 2010 at 8:32 am
I am trying to update a table with 37 million rows. I wrote the following code, hoping to avoid problems with the log and/or tempdb running out of space.
I was thinking this code would update 500,000 records with a null Modified_Date and then loop to the next set of records. It appears that it is simply modifying the same 500,000 records over and over.
Instead of using TOP, I was using ROWCOUNT, however, it appears to have the same effect.
I would appreciate any suggestions you may have.
Rob
declare @rows int
select @rows = 1
begin tran
while @rows > 0
begin
UPDATE TOP (500000)
EOLI
SET
Modified_Date = COALESCE(EPOL.Update_Date, GETDATE())
from
EOLI LEFT OUTER JOIN AS EPOL
ON EOLI.ORDER_ID = EPOL.ORDER_ID
WHERE
EOLI.Modified_Date is null
select @rows = @@rowcount
commit tran
if @rows >0
begin tran
end
October 3, 2010 at 1:25 pm
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://qa.sqlservercentral.com/Forums/Topic997293-391-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply