x

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
more ▼

asked Apr 16, 2012 at 05:25 PM in Default

avatar image

binodbabu
290 10 10 15

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)
TOP (Transact-SQL)
SET ROWCOUNT (Transact-SQL)

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.

more ▼

answered Apr 16, 2012 at 06:37 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

i dont want to update table at once coz there are millions of record so i want to update in chunk. how can i do that?

Apr 16, 2012 at 06:46 PM binodbabu

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.

Apr 16, 2012 at 06:57 PM Pavel Pawlowski

i want to run that script in production server.. if we update millions of record at once transaction log will be full so i want to update in batch ( in my example 2 records at a time) how can i do that..

Thanks in advance

Apr 16, 2012 at 07:22 PM binodbabu

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.

Apr 16, 2012 at 07:49 PM Pavel Pawlowski

oh ok got it thank you.

Apr 16, 2012 at 07:59 PM binodbabu
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x393

asked: Apr 16, 2012 at 05:25 PM

Seen: 7746 times

Last Updated: Apr 16, 2012 at 07:59 PM

Copyright 2016 Redgate Software. Privacy Policy