Re: Resequencing a table
David Paulo wrote:
>
> We have a to do list stored in a table much simplified as follows:
>
> Create Table TASKS
> (taskcode CHAR(6)
> ,stepno DECIMAL(9,0)
> ,description char(32)
> )
No guarantees on the efficiency of the following,
but interesting nonetheless(untried, untested):
select t1.taskcode, t1.stepno,
(select count(*)
from tasks t2
where t1.taskcode=t2.taskcode
and t2.stepno <= t1.stepno)*10 newstepno
from tasks t1
into temp tmp_tasks with no log
create index tmp_task_idx on tmp_tasks(taskcode, stepno)
update tasks
set stepno=
(select newstepno
from tmp_tasks
where tasks.taskcode=tmp_tasks.taskcode
and tasks.stepno=tmp_tasks.stepno)
Good Luck,
Douglas Wilson