Mass Update\Replace within Transaction

Question

Post reply

Mass Update\Replace within Transaction

sql.geek

SSC Enthusiast

Points: 134
More actions
October 1, 2010 at 3:09 am

#228929

Hello all,
Looking for some help\advice with a mass table update I need to make.
I need to issue an update on around 8 million rows, using a replace on a varchar col:
e.g.
update mytable
set myfield= replace(myfield,'oldtext','newtext')
I'm looking to 'batch' this transaction to control the transaction log size and aide performance, however I'm having trouble with examples online using the rowcount feature.
e.g.
set rowcount 10000
declare @rc int
set @rc=10000
while @rc=10000
begin
begin tran
-- code here
commit tran
end
This doesn't seem to work with update etc, as it appears to loop around the same set of results as defined in the batch size.
Could someone offer a solution?
Many thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

you've got to use a counter or a where statement which preventrs teh same row from being processed.

for example, if your table has an identity column:

dim @start int

dim @end int

SELECT @start = MIN(TableID),@end = MAX(TableID) From MyTable

while @start < @end

begin

begin tran

update mytable

set myfield= replace(myfield,'oldtext','newtext')

WHERE TableID BETWEEN @start AND @start + 10000

commit tran

SET @start = @start + 10000

end

an alternative is an additional where statement, something like:

update mytable

set myfield= replace(myfield,'oldtext','newtext')

WHERE mytable.myfield NOT LIKE '%somestring that appears in newtext%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql.geek SSC Enthusiast Points: 134 More actions · Answer 2

Thanks for that. I'll have a play with what you advise. 🙂

drew.allen SSC Guru Points: 77014 More actions · Answer 3

Lowell (10/1/2010)
update mytable set myfield= replace(myfield,'oldtext','newtext') WHERE mytable.myfield NOT LIKE '%somestring that appears in newtext%'

It's better to use

update mytable

set myfield= replace(myfield,'oldtext','newtext')

WHERE mytable.myfield LIKE '%oldtext%'

because it's possible to have enough records of batch size that contain neither oldtext or newtext and you will repeatedly loop on those records. Using LIKE '%oldtext%' you will only consider records that actually need to be changed, whereas using NOT LIKE '%newtext%' you will consider records that still need to be changed and records that never needed to be changed.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA