Update of certain number of 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

Transact-SQL (2000)

Update of certain number of rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-22 : 07:51:35

Harold writes "I would like to update, let's say, first 500 rows in a table and set the batch id of these first 500 rows to 1, second 500 to 2 and so on. how could I do it through SQL?"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-22 : 08:15:20

Here's one way to do it. You'll have to define the "order" of the table. In this example I have a RowID column as the PK. It is seeded at a high number and skips every other number to simulate an actual table with gaps in the IDs. But the concept will work on whatever you unique key is.


set nocount on
use pubs
go
--create a fake table and add some rows
create table junk (rowid int identity(300000,2) primary key, i int not null, batchid int null)
go
declare @i int
set @i = 1
while @i < 1511
begin
	insert junk (i) values (@i)
	set @i = @i+1
end

go
--Do the BatchID update in batches of 500
declare @startRowid int
	,@endRowid int
	,@batchid int
select @startrowid = min(rowid)
	,@endRowid = max(rowid)
	,@batchid = 1
from	(select top 500 rowid
	from	junk
	order by rowid) a

while @endRowID is not null
begin
	update junk set
		batchid = @batchid
	where	rowid >= @startRowid
	and	rowid <= @endRowid		

	select @startrowid = min(rowid)
		,@endRowid = max(rowid)
		,@batchid = @batchid + 1
	from	(select top 500 rowid
		from	junk
		where	rowid > @endRowid
		order by rowid) a	
end

select batchid, count(*) [BatchCount]
	,min(rowid) startRowid
	,max(rowid) endrowid
from junk group by batchid 
order by 1

go
drop table junk

Be One with the Optimizer
TG

mr_mist
Grunnio

1870 Posts

Posted - 2005-12-22 : 09:22:34

Or you could use SET ROWCOUNT

-------
Moo. :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-22 : 10:21:43

quote:
Originally posted by mr_mist

Or you could use SET ROWCOUNT

Yeah, that's better. That also simplifies it if you have a composit key (or even if you have no unique constraint, which would be bad)

Here's a way using rowcount:


set nocount on
use pubs
go
--create a fake table and add some rows
create table junk (rowid int identity(300000,2) primary key, i int not null, batchid int null)
go
declare @i int
set @i = 1
while @i < 1511
begin
	insert junk (i) values (@i)
	set @i = @i+1
end

go
--Do the BatchID update in batches of 500
declare @batchid int
set rowcount 500
select @batchid = 0
while @@rowcount > 0
begin
	set @batchid = @batchid + 1

	update junk set 
		batchid= @batchid
	where	batchid is null
end
set rowcount 0

select batchid, count(*) [BatchCount]
	,min(rowid) startRowid
	,max(rowid) endrowid
from junk group by batchid 
order by 1

go
drop table junk

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources