update TOP ? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

update TOP ?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-02-26 : 17:23:12

Hi,

I have a table with about 300 million rows, I have added a new column and its all NULL's now. I need to update them all to an integer value

update tblProfileViews_Users set visible = 1 where visible is null

I can't run this all at the same time because it brings the server to a halt. How can I execute this 20,000 rows at a time ?

Any help much appreciated!

thanks again,
mike123

CREATE TABLE [dbo].[tblProfileViews_Users](
[viewID] [int] IDENTITY(1,1) NOT NULL,
[viewerUserID] [int] NOT NULL,
[profileUserID] [int] NOT NULL,
[viewDate] [smalldatetime] NOT NULL,
[visible] [tinyint] NULL
) ON [PRIMARY]

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-26 : 17:27:18

Can't you just loop through viewID? Sort of like this:

DECLARE @min int, @max int, @maxViewId int

SELECT @min = 1, @max = @min + 20000, @maxViewId = MAX(ViewID)
FROM tblProfileViews_Users

WHILE @max <= @maxViewId
BEGIN

UPDATE ...
WHERE ... AND viewID BETWEEN @min AND @max

SELECT @min = @min + 20000, @max = @min + 20000

END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog