increment within update

[dummies2]

how do you update a column in database and then increase the number by 1
like this
S.id Name
3 n
5 a
6 b

now i want to make an update statment that would make it become
S.id Name
1 n
2 a
3 b

i have
update tablename set columname = columname + 1;
but this only make it
S.id Name
4 n
6 a
7 b

plz help

[DanInManchester]

What you are describing sounds like expected behaviour but I'm not undestanding the relationshp between :

1 n
2 a
3 b

3 n
5 a
6 b

Also from your table description it would appear these are ID's which typically you wouldn't update manually.

[dummies2]

assume that sid is not primary key but the other one is. so that should not get update automatically

[DanInManchester]

okay but how do you get from

3 to 1
5 to 2
6 to 3

I'm not seeing a pattern or relationship.
If you could clarify this it shouldn't be to hard.

[dummies2]

those 3 6 5 number are just random number that i put in there. Then now i come back and say. ok i dont want those number i want it to be in an order so i want to change it to 1 2 3

[brucevde]

This might work for you

Update TableName
Set Id = (Select Count(*) From TableName T2 Where T2.Id <= T1.Id )
From TableName T1

It assumes the original order of the Id column should be maintained and the Id column is unique, ie

3 -> 1
5 -> 2
6 -> 3
11 -> 6
8 -> 5
7 -> 4

[DanInManchester]

okay well if there is no direct relationship between the current value and it's new value you won't be able to do it that way.

You are probably going to have to update individual updates unless there is some other relationship? even then it could be come complicated through use of sub queries etc.


What database are you using?

[dummies2]

ms sql
but i took the advice from making the column automatic number ty

[dummies2]

sorry bruce i forgot to read your post. ty though.