TSQL while loop update batch

Question

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

Answer 1

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)
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.

answered Apr 16 '12 at 06:37 PM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

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 '12 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 '12 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 '12 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 '12 at 07:49 PM Pavel Pawlowski

oh ok got it thank you.

Apr 16 '12 at 07:59 PM binodbabu

(comments are locked)

TSQL while loop update batch

Follow this question

Related Questions