Batch Update on a very huge table

Those of us who work in the Production / Live DB , know the pain , of firing a update query on a huge table . This is very problematic as this will lead to the following

• Lock escalation : The entire table will be locked by your application , so other users /  application will not be able to perform the DDL on this table
• The TempDB will grow huge
• A update lock on a table will stop other users , for making changes in the table

I have written a below snippet which will help us fight the above issues

-- Step 1 : Declare the varaibles

use DBNAME
Declare @counter int
Declare @RowsEffected int
Declare @RowsCnt int
Declare @CodeId int
Declare @Err int
SELECT @COUNTER = 1
SELECT @RowsEffected = 0
 

/*
Step 2 : Get the value of the Code , with which we need to update the existing Code . In my case I am capturing is from a table , we can always hard code it .
*/

SELECT @CodeID = CodeID FROM CODE WHERE XXXX ='YYYY'


/*
Step 3: Start the while loop ,  if we have 100,000 records , and in each loop 5,000 records will be update , so the total number of cycle will be 100,000/5000 i.e 20
*/

WHILE ( @COUNTER > 0)
BEGIN
SET ROWCOUNT 5000

-- Note : The SET ROWCOUNT 5000   will just pick the top 5000 records */

   
      /*UPDATING TABLE */
  UPDATE Table
SET CodeID= @CodeID
WHERE Codeid = @OldCode
SELECT @RowsCnt = @@ROWCOUNT ,@Err = @@error
IF @Err <> 0
BEGIN
Print 'Problem Updating the records'
END
IF @RowsCnt = 0
SELECT @COUNTER = 0
ELSE

/* Increment the Counter */

  SELECT @RowsEffected = @RowsEffected + @RowsCnt
PRINT 'The total number of rows effected :'+convert(varchar,@RowsEffected)

            /*delaying the Loop for 10 secs , so that Update is comepleted*/    

     WAITFOR DELAY '00:00:10'         
 END

--Step 4 : Check if all the records are updated or not .
IF EXISTS ( SELECT CodeID
FROM Table (NOLOCK)
WHERE CodeID = @OldCodeid
)
BEGIN
PRINT ('All the records are not updated , there is some problem , Contact Devs ')
END
BEGIN
PRINT ('All the records are updated SUCCESSFULLY !!!!')
END
/* ------Set rowcount to default ----*/
SET ROWCOUNT 0