Re: renumbering the data in a column?

From: ddf <oratune_at_msn.com>
Date: Wed, 14 Jan 2009 13:00:24 -0800 (PST)
Message-ID: <b2588b20-1b61-4ab0-82f3-a12a6ad4b978_at_z6g2000pre.googlegroups.com>

On Jan 14, 2:02 pm, m..._at_pixar.com wrote:
> I have some rows that I would like to renumber.  For example:
>
>     select * from t where name like 'x%' order by seq;
>
>     seq    name
>     ---    ----
>      15    xfoo
>      17    xbar
>      26    xbaz
>
> desired state, seq column renumbered starting at 1:
>
>     seq    name
>     ---    ----
>       1    xfoo
>       2    xbar
>       3    xbaz
>
> Is there a single sql statement that can do this?  Or should I
> just loop over the result set and manually update the seq
> for each row?
>
> Many TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

This might get you started:

SQL> create table renum(

  2          seq     number,
  3          name    varchar2(10)

  4 );

Table created.

SQL>
SQL> insert all
  2 into renum
  3 values(15,'xfoo')
  4 into renum
  5 values(17, 'xbar')
  6 into renum
  7 values(26, 'xbaz')
  8 select * From dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select seq, name, rank() over (order by seq) rk   2 from renum;

       SEQ NAME RK
---------- ---------- ----------

        15 xfoo                1
        17 xbar                2
        26 xbaz                3

SQL>
SQL> update renum
  2 set seq = (select num.rk from

  3                  (select seq, rank() over (order by seq) rk
  4                          from renum) num
  5                  where num.seq = renum.seq)

  6 /

3 rows updated.

SQL>
SQL> select *
  2 from renum;

       SEQ NAME
---------- ----------

         1 xfoo
         2 xbar
         3 xbaz

SQL> You may need to modify the code for your actual data, but it, hopefully, gives you a push in the right direction.

David Fitzjarrell Received on Wed Jan 14 2009 - 15:00:24 CST