UPDATE Every record in column but group by,,

[loydall]

Hi,

Let's say my table looks like this

id | groupName | pNumber
------------------------
1 | 1 | 1
2 | 1 | 1
3 | 1 | 1
4 | 2 | 1
5 | 2 | 1
6 | 3 | 1
7 | 3 | 1
8 | 3 | 1

I want to update pNumber so that, for each pNumber in a groupName, it increments by 1

so the resulting table would look like

id | groupName | pNumber
------------------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 1
5 | 2 | 2
6 | 3 | 1
7 | 3 | 2
8 | 3 | 3

See?

I'm guessing the update will obviously group by groupName but not sure exactly how the update will look?

Any ideas?

 

[PHV]

You may try something like this:

UPDATE yourTable A
SET pNumber=(SELECT COUNT(*) FROM yourTable B WHERE B.groupName=A.groupName AND B.id<=A.id)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886