number generator via an update query

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)

number generator via an update query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2007-12-20 : 08:33:24

I have created a new column called match_code within my contact table. I want to programmatically assign a value to this column starting at 0 and increasing by one for each record (7000 records total) in the contact table via an update statement. Can someone help me out with this type of query?

Happy Holidays!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-20 : 08:34:46

use row_number() to do it

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 08:37:02

Update table
Set match_code=ROW_NUMBER() OVER (ORDER BY pk) -1

pk is the primary key of the table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-20 : 08:43:51

quote:
Originally posted by visakh16

Update table
Set match_code=ROW_NUMBER() OVER (ORDER BY pk) -1

pk is the primary key of the table

You can't do that for row_number().
you will get this error

quote:
Server: Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Use this


update	t
set	colb = row_no
from	(
		select	cola, colb, row_number() over (order by cola) as row_no
		from	table
	) t

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 09:34:13

quote:
Originally posted by qman

I have created a new column called match_code within my contact table. I want to programmatically assign a value to this column starting at 0 and increasing by one for each record (7000 records total) in the contact table via an update statement. Can someone help me out with this type of query?

Happy Holidays!

Why not have that in your select statement?

Select row_number()-1 over(order by col), othercols from table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources