Incorrect ID numbers.

[outofservice]

I have a sql2k table with columns "line_num", "task_index" and "task".

The table is basically a list of tasks that need to be completed in a specific order. "Line_num" should always read 0, 1 etc and is incorrect (Column "Task" and "task_index" are correct). The incorrect "line_num" data looks like this:

Task_index Line_num Task
125 0 Sound alarm
125 2 Cordon off area
125 1 Evacuate area
129 1 Locate rescue leader
129 0 Establish casualties
145 2 Call coastguard
145 0 Search & rescue standby
145 1 Notify rescue divers

and so on ...
It should look like this ...

Task_index Line_num Task
125 0 Sound alarm
125 1 Cordon off area
125 2 Evacuate area
129 0 Locate rescue leader
129 1 Establish casualties
145 0 Call coastguard
145 1 Search & rescue standby
145 2 Notify rescue divers

Any ideas on how to update "Line_num" so that it increments correctly within each task??



Lauryn Bradley
SQL Server DBA

 

[ClaireHCM]

This is the query
select * from table_name order by Task_index,Line_num
---------------------------------------------------------
If you want to update the whole table....Try the following!

1.create table temp(just some table name) with same coulmn and datatype

2.insert into temp select * from table_name order by Task_index,Line_num

3.check whether the temp table is the table order you want and everything.

4.delete original table

5.insert into original table select * from temp table