Divide update into batches
-
Hi all -- first time post. My problem seems elementary, but I am having trouble producing the TSQL to make it work.
I need to update a few hundred thousand records in a table that is read heavily. To do this, I would like to divide my update statement such that is runs in batches, affecting a specified number of records per iteration. Below is what I have thus far. It does appear to work, but gets stuck in a loop. Unfortunately, my troubleshooting also appears to be stuck in a loop, hence my request for assistance. Thanks.
DECLARE @x INT
DECLARE @cnt INT
SET ROWCOUNT 100000
SET @x = 1
SET @cnt = 0
WHILE @x > 0
BEGIN
Update Listing Set
Status = 'Deleted',
Min = .05
Where
AccountID in (Select AccountID from Account WITH (NOLOCK) where providerID = 70)
and IsActive = 0
SET @x = @@rowcount
SET @cnt = @cnt + @x
END
-
I'm afraid there's a silly @cnt in your code, which just gets bigger and doesn't do anything else. There is nothing in the code to tell it which records have been updated, so it keeps rerunning the same statement, and getting > 0 records affected.
Put a where clause checking that status != 'deleted' or Min != .05
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Have you tried
SET @x = @@rowcount
PRINT @x
SET @cnt = @cnt + @x
to see what @x gives?
Otherwise
WHILE EXIST (SELECT * FROM Account WITH (NOLOCK) WHERE ProviderID = 70 AND IsActive = 0)
UPDATE Listing
SET Status = 'Deleted',
Min = .05
WHERE AccountID IN (SELECT TOP 100000 AccountID FROM Account WITH (NOLOCK) WHERE ProviderID = 70 AND IsActive = 0)
N 56°04'39.16"
E 12°55'05.25" -
Peter, yours will keep repeating the same update, too
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
True. I didn't really think...
WHILE EXIST (SELECT * FROM Account WITH (NOLOCK) WHERE ProviderID = 70 AND IsActive = 0 AND Status <> 'Deleted')
UPDATE Listing
SET Status = 'Deleted',
Min = .05
WHERE AccountID IN (SELECT TOP 100000 AccountID FROM Account WITH (NOLOCK) WHERE ProviderID = 70 AND IsActive = 0 AND Status <> 'Deleted')
N 56°04'39.16"
E 12°55'05.25" -
DECLARE @N int, @Cnt int, @Range int
SELECT Identity(int, 1,1) as id, AccountID
INTO #ActiveAccounts
FROM Account WITH (NOLOCK)
WHERE ProviderID = 70 AND IsActive = 0
SELECT @Cnt = @@Rowcount
SET @N = 1
SET @Range = 1000 -- this may be a parameter for your SP
WHILE @N <= @Cnt
BEGIN
UPDATE L
SET Status = 'Deleted',
Min = .05
FROM Listing L
INNER JOIN #ActiveAccounts A ON L.AccountID = A.AccountID
WHERE A.Id between @N and @Range
SET @N = @N + Range
WAITFOR DELAY '00:00:01' -- To allow other processed to theit job
END
_____________
Code for TallyGenerator -
INNER JOIN #ActiveAccounts A ON L.AccountID = A.AccountID
WHERE A.Id between @N and @N + @Range - 1
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Yes, of course.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply