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

binodbabu gravatar image

binodbabu
290 8 10 12

(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)][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.

[3]: http://msdn.microsoft.com/en-us/library/ms188774.aspx
more ▼

answered Apr 16, 2012 at 06:37 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x290

asked: Apr 16, 2012 at 05:25 PM

Seen: 4502 times

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