Re: renumbering the data in a column?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 15 Jan 2009 06:15:54 -0800 (PST)
Message-ID: <472b7f1d-7081-4e0f-a5b7-353ada0f407b_at_r24g2000vbp.googlegroups.com>

On Jan 14, 4:00 pm, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Mark, David's plan is fine providing the sequence is not a PK or UK referenced by a FK constraint, but why do you need to re-number the sequence column? If the sequence field is a key then generated key values are arbitrary in nature and gaps should not matter. The reason I am asking has more to do with how the value will be handled going forward rather than the 'fix' being made.

HTH -- Mark D Powell -- Received on Thu Jan 15 2009 - 08:15:54 CST