Performing an UPDATE on only N rows

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

SQL Server Development (2000)

Performing an UPDATE on only N rows

Author

Topic

NewMedia42
Starting Member

35 Posts

Posted - 2004-05-05 : 16:22:49

Is it possible to perform an update on only a certain number of rows? For example, let's say I have a table with 100 rows all with a field called Hit set to 0; I could use the following to update all of them:

UPDATE [Table] SET (Hit=1) WHERE (Hit=0)

Would it be possible to have it only update the first say, 10 rows that meet the criteria? So when I performed the query, I would end up with 90 rows with Hit set to 0, and 10 with hit set to 1?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-05 : 16:24:25

SET ROWCOUNT 10

UPDATE [Table] SET (Hit=1) WHERE (Hit=0)

SET ROWCOUNT 0

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-05 : 16:27:47

Another method:

update [table]
set hit = 1 
where [id] in
(
	select top 10 [id] 
	from [table]
	where hit = 0
	order by [id] asc
)

NewMedia42
Starting Member

35 Posts

Posted - 2004-05-05 : 17:33:03

Thanks!

Is there any benefit/drawbacks between the two methods?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources