We need to update every row in a busy production OLTP database table that contains approximately 50 million rows

We need to update every row in a busy production OLTP database table that contains approximately 50 million rows. Any suggestionson how we may approach this?
tesla764Asked:
Who is Participating?
 
John ClaesConnect With a Mentor Senior .Net Consultant & Technical AnalistCommented:
I see 2 different approches. that are not to heavy upon a busy environment.


1 make a cursor and flow for every records over the update and make sure you commit
(Is not my proposal)


2)
Make a Stored procedure
let this run the folowing script until it returns 0 changed records


Update TABLE
Set Field='Value'
where TABLEID in (SELECT TOP 100 TABLEID from TABLE (nolock) where Field<>'Value')



the nolock option will make sure that the values are taken directly from the database and not from the TRansactionSet - this is a fast Query and we can fastly execute.
Execution will take some time, but no User will notice it


0
 
Aaron ShiloChief Database ArchitectCommented:
hi

option 1:

set rowcount 1000

update mytable
set value = @value.
where value <> @value

option 2:
do this in small controlled chunks.
use the PK to control the rows you retrive and update every batch and move forward.
0
 
tesla764Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.