Re: numbering function

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 6 Aug 1999 05:34:00 GMT
Message-ID: <7ods48$p9a$4@news.seed.net.tw>

Alessandro Bonetti <ABonetti_at_yahoo.com> wrote in message news:Qx2q3.1489$001.715973_at_WReNphoon3...
> Hi there,
> I've a stupid question, but I'm new using Personal Oracle 7.3
>
> I've a 100,000 record table. I've a field named Num and I want to
> automatically fill it starting from 1 to the number of records. The table
> must be ordered for a certain criteria before numbering. How can I do? I
> have to use a single command to put in my Delphi program.
> Thanx in advance

If your table is already ordered, you can simply use:

update table_name set column_name=rownum;

If not, see the following sample code:

declare
  cursor c is
    select * from emp
    order by deptno, ename
    for update of empno;
  i number;
begin
  for r in c loop
    i:=c%rowcount;
    update emp set empno=i where current of c;   end loop;
end;
/ Received on Fri Aug 06 1999 - 00:34:00 CDT