Updating with ROW_NUMBER() - 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

General SQL Server Forums

New to SQL Server Programming

Updating with ROW_NUMBER()

Author

Topic

Mortal Wombat
Starting Member

10 Posts

Posted - 2011-09-23 : 07:11:34

Hi All,

I'm trying out the use of the ROW_NUMBER() function but a little confused about a result I am seeing when trying to UPDATE a table using it.

Given the simple table "people"
-------------------------
name number
-------------------------
john 0
mary 0
kent 0
homer 0

I am trying to populate the number column using the following:



update people set number = tmp.number
from 
     (
	Select
	     ROW_NUMBER() OVER(ORDER BY name ) as [number]
	from
	     people		
      )as tmp

I was expecting the people table to be updated with sequential numbers 1,2,3,4.

However, what I am actually getting is 1,4,1,4.

I can't understand the process of how this is happening, can anyone help point me in the right direction?

Thanks.

Kristen
Test

22859 Posts

Posted - 2011-09-23 : 07:27:48

This perhaps?


update U
set number = tmp.number
from people AS U
    JOIN
     (
	Select
	     ROW_NUMBER() OVER(ORDER BY name, P.MyID ) as [number],
             P.MyID
	from
	     people AS P		
      )as tmp 
        ON tmp.MyID = U.MyID

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-23 : 07:31:09

[code]update people set number = tmp.number
from
(
Select name,
ROW_NUMBER() OVER(ORDER BY name ) as [number]
from
people
)as tmp
inner join people as p
on p.name = tmp.name
[/code]

Kristen is faster than me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 07:34:02

[code]update people set number = tmp.rn
from
(
Select
ROW_NUMBER() OVER(ORDER BY name ) as rn,number
from
people
)as tmp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-23 : 07:36:20

Interesting though Russell, I didn't think of how you have addresses the problem.

Yours would give the same number to people with the same name (may depend a bit on how SQL orders duplicates on NAME column), and leave gaps ... a bit like the difference between RANK and DENSE RANK.

That may be what the O/P wants, rather than contiguous numbering of all rows, in name order

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-23 : 07:42:22

Yes, yours is better. I just went with the data OP posted.

Kristen
Test

22859 Posts

Posted - 2011-09-23 : 07:53:43

"yours is better"

We have each answered a different question. Not clear to me what the O/P actually wants.

The O/P does say "I was expecting the people table to be updated with sequential numbers 1,2,3,4." but the sample data has no duplicate names ...

You'll know me by my pedanticness!

Mortal Wombat
Starting Member

10 Posts

Posted - 2011-09-23 : 08:24:40

Thanks for the help guys,

Inner joining back to the people table does seem to have fixed my issue, much appreciated.

I am still a bit baffled as to how my original output updated the number column to 1,4,1,4?

There was no (ORDER BY ... DESC) so how on earth does my output result how it did?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 08:57:26

This is the absolutely the fastest way (if you don't care about a special order, just sequential).


DECLARE @rn INT

SET @rn = 0

UPDATE dbo.Table1 SET @rn = number = @rn + 1

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 09:00:02

quote:
Originally posted by Mortal Wombat

There was no (ORDER BY ... DESC) so how on earth does my output result how it did?

Just mere coincident since there is no correlation at all between people table and tmp table.
If you case about the order of sequential update, do this

UPDATE	f
SET	number = rn
FROM	(
		SELECT	number,
			ROW_NUMBER() OVER (ORDER BY name) as rn
		FROM	dbo.People		
      ) as f

N 56°04'39.26"
E 12°55'05.63"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-23 : 09:17:40

quote:
Originally posted by SwePeso

This is the absolutely the fastest way (if you don't care about a special order, just sequential).

DECLARE @rn INT

SET @rn = 0

UPDATE dbo.Table1 SET @rn = number = @rn + 1

N 56°04'39.26"
E 12°55'05.63"

This is the quirky update right? I thought we didn't advocate its use as it's undocumented, unrelational and...well...quirky.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 09:21:01

No, this is not the quirky update. The quirky update implies a sorting order.
The syntax above is documented in Books Online. And as I wrote, there is no guaranteed order in the method.
The only sure thing is that numbers are sequential but no necessarily in the order of names, or anything else for that matter.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 09:31:46

For reference to Books Online see here http://msdn.microsoft.com/en-us/library/ms177523.aspx
and the line "@variable = column = expression"

quote:
@variable
Is a declared variable that is set to the value returned by expression.

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources