[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Loop and update statement in batches

i would like to run this statement, 10,000 at a time, commit then get the next t0,000

update tbl1
set filed2 = 0

there are more than 100,000,000 rows, so i don't want to have one big commit statment, and fill up the transaction log. what is the best way to do this ?
0
Auerelio Vasquez
Asked:
Auerelio Vasquez
1 Solution
 
JestersGrindCommented:
You want to use SET ROWCOUNT 10000 before your UPDATE statement.  Just loop through until there are no records where filed2 = 2.  Something like this.

WHILE EXISTS(SELECT * FROM tbl1 WHERE filed2 <> 0)

BEGIN

SET ROWCOUNT 10000

UPDATE tbl1 SET filed2 = 0

END

Greg

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now