asked
TSQL while loop update batch
HI: I want to update my table in chunck.. (if i have 10 rows i want to update 2/2) where did i do mistake in this script. Could you please guide me. CREATE TABLE #a ( web_ref_no INT , customer_no INT , Purchase_reqID INT ) INSERT INTO #a VALUES ( 1, 101, 1 ) INSERT INTO #a VALUES ( 2, 102, 2 ) INSERT INTO #a VALUES ( 3, 103, 3 ) INSERT INTO #a VALUES ( 4, 104, 4 ) INSERT INTO #a VALUES ( 5, 105, 5 ) INSERT INTO #a VALUES ( 6, 106, 6 ) INSERT INTO #a VALUES ( 7, 107, 7 ) INSERT INTO #a VALUES ( 8, 108, 8 ) INSERT INTO #a VALUES ( 9, 109, 9 ) INSERT INTO #a VALUES ( 10, 110, 10 ) CREATE TABLE #B ( web_ref_no INT , customer_no INT , purchase_reqID INT ) INSERT INTO #b VALUES ( 1, 101, NULL ) INSERT INTO #b VALUES ( 2, 102, NULL ) INSERT INTO #b VALUES ( 3, 103, NULL ) INSERT INTO #b VALUES ( 4, 104, NULL ) INSERT INTO #b VALUES ( 5, 105, NULL ) INSERT INTO #b VALUES ( 6, 106, NULL ) INSERT INTO #b VALUES ( 7, 107, NULL ) INSERT INTO #b VALUES ( 8, 108, NULL ) INSERT INTO #b VALUES ( 9, 109, NULL ) INSERT INTO #b VALUES ( 10, 110, NULL ) DECLARE @counter INT DECLARE @numOfRecords INT DECLARE @batchsize INT SET @numOfRecords = ( SELECT COUNT(*) AS NumberOfRecords FROM #b WITH ( NOLOCK ) ) SET @counter = 0 SET @batchsize = 2 SET ROWCOUNT @batchsize WHILE @counter < ( @numOfRecords / @batchsize ) + 1 BEGIN BEGIN TRANSACTION SET @counter = @counter + 1 UPDATE #b SET #b.purchase_reqID = #a.purchase_reqID FROM #a WITH ( NOLOCK ) INNER JOIN #b WITH ( NOLOCK ) ON #a.customer_no = #b.customer_no AND #a.web_ref_no = #B.web_ref_no IF @@ERROR != 0 BEGIN PRINT @@ERROR PRINT 'ERROR' ROLLBACK TRANSACTION RETURN END ELSE BEGIN COMMIT TRANSACTION PRINT 'COMMITTED SUCCESSFULLY' END END SET ROWCOUNT 0
The problem is, that you are not distinguishing between already updated and not updated records. So the issue is in your `UPDATE` statement as it updates in the circle the first two rows. Based on your example, you can put eg. a condition `#b.Purchase_reqID IS NULL` and the update command will be as follow: UPDATE #b SET #b.purchase_reqID = #a.purchase_reqID FROM #a WITH ( NOLOCK ) INNER JOIN #b WITH ( NOLOCK ) ON #a.customer_no = #b.customer_no AND #a.web_ref_no = #B.web_ref_no WHERE #b.Purchase_reqID IS NULL Anyway if you are working on SQL Server 2005 and above, you should use the `TOP ( expression )` clause instead of setting `ROWCOUNT`. UPDATE TOP(2) #b SET #b.purchase_reqID = #a.purchase_reqID FROM #a WITH ( NOLOCK ) INNER JOIN #b WITH ( NOLOCK ) ON #a.customer_no = #b.customer_no AND #a.web_ref_no = #B.web_ref_no WHERE #b.Purchase_reqID IS NULL See: [UPDATE (Transact-SQL)][1] [TOP (Transact-SQL)][2] [SET ROWCOUNT (Transact-SQL)][3] > Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. [1]:
http://msdn.microsoft.com/en-us/library/ms177523.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms189463.aspx [3]:
http://msdn.microsoft.com/en-us/library/ms188774.aspx
Exactly as you wrote it (or using the `TOP` clause instead of `SET ROWCOUNT`). But as I have mentioned, you need to include some condition to distinguish already updated records when you are updating the rows in batch. If you do not put the condition into the update query, your query will update in the circle only the first two records. Once you put the condition there, already updated records will not be updated again during next loop, but another records will be processed by subsequent loops.
We are writing in a circle.. Yous should do this exactly as hou have done it in your original question. Only you need to add a condition to distinguish between already modified and not modified records as you execute the update command in loop. If you do not include the condition, then it will not work as it will update only the first x records in each loop.
4 People are following this question.
