update only 10000 rows at a time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

update only 10000 rows at a time

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-03-05 : 11:16:08

I have one table which has 40 million rows. I am updating some columns in it using a join with another table and want to update only 10,000 rows at a time.

Kindly help.

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-03-05 : 11:42:12

You can use the TOP command with UPDATE.

Something like
UPDATE TOP (10000) yt
SET yt.Field = ''
FROM YourTable yt
INNER JOIN YourOtherTable yot ON yt.ID=yot.ID

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-05 : 11:44:20

You can use SET ROWCOUNT or TOP 10000 clause. Having said that, don't use SET ROWCOUNT, it is deprecated. http://msdn.microsoft.com/en-US/library/ms188774(v=sql.105).aspx

In either case, you will need logic to exclude the ones already updated. That can cause performance issues unless that logic is efficient (i.e., can use an index to find the next set of rows to be updated).

You want to do it in multiple batches (with batch separators in between). If you use a while loop, that sort of defeats the purpose.

If the size of the log file is a concern, slicing up the updates will not help if your recovery model is not SIMPLE.

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-03-05 : 11:46:00

Sorry I hadn't finished...

Then You'd need to add a where clause so you didn't keep updating the same rows. If you have a DateUpdated field, you could use that. If you don't then unless you've a foolproof way of picking out fields you've updated, I suggest you should add a DateUpdated field for this purpose.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-05 : 11:48:12

quote:
Originally posted by theboyholty

Sorry I hadn't finished...

Then You'd need to add a where clause so you didn't keep updating the same rows. If you have a DateUpdated field, you could use that. If you don't then unless you've a foolproof way of picking out fields you've updated, I suggest you should add a DateUpdated field for this purpose.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Now I don't feel so bad. Otherwise, I was going to add a

to my post.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 12:23:02

It would help if we saw the DDL of the tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-05 : 15:20:55

First thing is add a indexed rownumber field, then...


declare @bottom int, @top int,@total int,@Records int

set @Records = 10000
set @bottom = 1
set @top = @Records

set @total = (Select count(*) from mytable)

while @bottom < @total
begin
 Update a
 set a.myfield = 'WHATEVER'
,a.myfield2 = 'whatever2'
 from
 Mytable a
 where rowidfield between @Bottom and @Top

set @Bottom = @Bottom + @Records
set @Top =  @Top + @Records
end

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources