Renumber column as a sql 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

General SQL Server Forums

New to SQL Server Programming

Renumber column as a sql query

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-03-02 : 09:02:50

I'm googling this requirement but not getting very far.

All I need to do is renumber a field after a delete.

Looks like I set it to be an identity field. But it must be an sql query - not a SP.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-02 : 09:09:41

Are you saying you have a field similar to an autonumber and when you delete some rows you want every row to be numbered sequencially without breaks?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:11:00

renumber to what? do you mean filling the gaps?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 09:11:14

quote:
Originally posted by insanepaul

I'm googling this requirement but not getting very far.

All I need to do is renumber a field after a delete.

Looks like I set it to be an identity field. But it must be an sql query - not a SP.

If you worry about having gaps in numbers, dont use identity column

After delete

update table
set col=col-1
where col>deleted_number

Madhivanan

Failing to plan is Planning to fail

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-03-02 : 09:16:06

quote:
Originally posted by darkdusky

Are you saying you have a field similar to an autonumber and when you delete some rows you want every row to be numbered sequencially without breaks?

Sorry to be more specific, i now have an identity field 'ID' and I need to keep the identity seed at 1 at all times:

ID
1
2
3
4
5

If I delete the second ID then ID 3 and 4 would be renumbered to 2 and 3 as below:

ID
1
2
3
4

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-03-02 : 09:17:04

quote:
Originally posted by madhivanan

quote:
Originally posted by insanepaul

I'm googling this requirement but not getting very far.

All I need to do is renumber a field after a delete.

Looks like I set it to be an identity field. But it must be an sql query - not a SP.

If you worry about having gaps in numbers, dont use identity column

After delete

update table
set col=col-1
where col>deleted_number

Madhivanan

Failing to plan is Planning to fail

OK, I can do as you suggest and switch off the identity....i wonder how to do it if it was identity.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:18:57

quote:
Originally posted by madhivanan

quote:
Originally posted by insanepaul

I'm googling this requirement but not getting very far.

All I need to do is renumber a field after a delete.

Looks like I set it to be an identity field. But it must be an sql query - not a SP.

If you worry about having gaps in numbers, dont use identity column

After delete

update table
set col=col-1
where col>deleted_number

Madhivanan

Failing to plan is Planning to fail

what if he deletes multiple records?i think a deleted trigger with below code will do


update t
set t.col=col-(select count(*) from deleted where col<t.col)
from table t
where t.col>(select min(col) from deleted)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 09:58:47

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by insanepaul

I'm googling this requirement but not getting very far.

All I need to do is renumber a field after a delete.

Looks like I set it to be an identity field. But it must be an sql query - not a SP.

If you worry about having gaps in numbers, dont use identity column

After delete

update table
set col=col-1
where col>deleted_number

Madhivanan

Failing to plan is Planning to fail

what if he deletes multiple records?i think a deleted trigger with below code will do

update t
set t.col=col-(select count(*) from deleted where col<t.col)
from table t
where t.col>(select min(col) from deleted)

Yes it is

Madhivanan

Failing to plan is Planning to fail

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-03-02 : 10:08:19

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by insanepaul

I'm googling this requirement but not getting very far.

All I need to do is renumber a field after a delete.

Looks like I set it to be an identity field. But it must be an sql query - not a SP.

If you worry about having gaps in numbers, dont use identity column

After delete

update table
set col=col-1
where col>deleted_number

Madhivanan

Failing to plan is Planning to fail

what if he deletes multiple records?i think a deleted trigger with below code will do

update t
set t.col=col-(select count(*) from deleted where col<t.col)
from table t
where t.col>(select min(col) from deleted)

As it happens I only need to delete 1 row at a time so will use the other sql query. But if the requirement changes and for future reference I can use your sql query if one deletes multiple rows.

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 10:35:19

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources