title: How to update large table with millions of rows in SQL Server?
body: <p>I've an <code>UPDATE</code> statement which can update more than million records. I want to update them in batches of 1000 or 10000. I tried with <code>@@ROWCOUNT</code> but I am unable to get desired result. </p>
  
  <p>Just for testing purpose what I did is, I selected table with 14 records and set a row count of 5. This query is supposed to update records in 5, 5 and 4 but it just updates first 5 records.</p>
  
  <p>Query - 1:</p>
  
  <pre><code>SET ROWCOUNT 5
  
  UPDATE TableName 
  SET Value = 'abc1' 
  WHERE Parameter1 = 'abc' AND Parameter2 = 123
  
  WHILE @@ROWCOUNT &gt; 0
  BEGIN
      SET rowcount 5
  
      UPDATE TableName 
      SET Value = 'abc1' 
      WHERE Parameter1 = 'abc' AND Parameter2 = 123
  
      PRINT (@@ROWCOUNT)
  END
  
  SET rowcount 0
  </code></pre>
  
  <p>Query - 2:</p>
  
  <pre><code>SET ROWCOUNT  5
  
  WHILE (@@ROWCOUNT &gt; 0)
  BEGIN
      BEGIN TRANSACTION
  
      UPDATE TableName 
      SET Value = 'abc1' 
      WHERE Parameter1 = 'abc' AND Parameter2 = 123
  
      PRINT (@@ROWCOUNT)
  
      IF @@ROWCOUNT = 0
      BEGIN
          COMMIT TRANSACTION
  
          BREAK
      END
  
      COMMIT TRANSACTION
  END
  
  SET ROWCOUNT  0
  </code></pre>
  
  <p>What am I missing here?</p>