shift records in the table

[rudi]

Hi,
I have a small table with 2 columns:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
4 Message#4
6 Message#6

Is there a way to write a SP or select statement, so that if there is a gap in the sequence of OrderNo of more then 1(OrderNo is not an identity field) then the table will look like:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
3 Message#4
4 Message#6

Thanks a lot,
rudi

[rudi]

What I really need ( in the above example) is to get rid off of the break in the sequence of the OrderNo by changing ( somehow )OrderNo #4 to OrderNo #3 , so if there is a missing record in between 2 and 4 then-->
OrderNo.....Description
......................
...2.........Message#2
...4.........Message#4

--> OrderNo #4 will become OrderNo #3( incremented by 1)
Thanks,
rudi


------------
rudi at 8/19/00 12:50:42 AM

Hi,
I have a small table with 2 columns:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
4 Message#4
6 Message#6

Is there a way to write a SP or select statement, so that if there is a gap in the sequence of OrderNo of more then 1(OrderNo is not an identity field) then the table will look like:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
3 Message#4
4 Message#6

Thanks a lot,
rudi

[Chris]

It would be much easier to just use an Identity column. If you require some other value than the (Max+1) or next number than set the identity_insert ON and insert the row, then set identity_insert off.

You can make OrderNo an identity by using ALTER TABLE

------------
rudi at 8/20/00 1:37:02 PM

What I really need ( in the above example) is to get rid off of the break in the sequence of the OrderNo by changing ( somehow )OrderNo #4 to OrderNo #3 , so if there is a missing record in between 2 and 4 then-->
OrderNo.....Description
......................
...2.........Message#2
...4.........Message#4

--> OrderNo #4 will become OrderNo #3( incremented by 1)
Thanks,
rudi


------------
rudi at 8/19/00 12:50:42 AM

Hi,
I have a small table with 2 columns:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
4 Message#4
6 Message#6

Is there a way to write a SP or select statement, so that if there is a gap in the sequence of OrderNo of more then 1(OrderNo is not an identity field) then the table will look like:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
3 Message#4
4 Message#6

Thanks a lot,
rudi

Thanks Chris!

------------
Chris at 8/22/00 11:28:21 AM


It would be much easier to just use an Identity column. If you require some other value than the (Max+1) or next number than set the identity_insert ON and insert the row, then set identity_insert off.

You can make OrderNo an identity by using ALTER TABLE

------------
rudi at 8/20/00 1:37:02 PM

What I really need ( in the above example) is to get rid off of the break in the sequence of the OrderNo by changing ( somehow )OrderNo #4 to OrderNo #3 , so if there is a missing record in between 2 and 4 then-->
OrderNo.....Description
......................
...2.........Message#2
...4.........Message#4

--> OrderNo #4 will become OrderNo #3( incremented by 1)
Thanks,
rudi


------------
rudi at 8/19/00 12:50:42 AM

Hi,
I have a small table with 2 columns:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
4 Message#4
6 Message#6

Is there a way to write a SP or select statement, so that if there is a gap in the sequence of OrderNo of more then 1(OrderNo is not an identity field) then the table will look like:
OrderNo Description
0 Message#0
1 Message#1
2 Message#2
3 Message#4
4 Message#6

Thanks a lot,
rudi