Table updation - SQL 2000 - 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 2000 Forums

Transact-SQL (2000)

Table updation - SQL 2000

Author

Topic

carumuga
Posting Yak Master

174 Posts

Posted - 2008-07-04 : 08:30:52

Hi,

I've millions of records something like this in the SQL Server 2000 database.

ID Version
--- -------
1 1
1 NULL
1 NULL
2 1
2 NULL
2 NULL

Please provide me an simple update query which update the NULL with the incremental value of version by grouping the ID and my updation should not affect my performance and keep in mind that i have millions of records.

My final output after updation should be like this:

ID Version
--- -------
1 1
1 2
1 3
2 1
2 2
2 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 02:13:43

select identity(int,1,1) as seq,* into #temp from yourtable order by id,Version desc

update t
set t.Version= (select count(*) from #temp
where id=t.id and seq<t.seq) + 1
from #temp t

select * from #temp
drop table #temp

carumuga
Posting Yak Master

174 Posts

Posted - 2008-07-10 : 03:06:21

Thanks it really works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources