Re: Update of all columns of a table - beginner need help-

Hello,

What version of Oracle you have?
In 8i you can try...

SQL> create table my_table(id number, my_date date);

Table created.

SQL> insert into my_table values(1,'1-JAN-1998');

1 row created.

SQL> insert into my_table values(2,'5-FEB-2000');

1 row created.

SQL> insert into my_table values(3,'3-NOV-1999');

1 row created.

SQL> insert into my_table values(4,'6-OCT-2000');

1 row created.

SQL> create sequence my_table_id start with 1 increment by 1;

Sequence created.

SQL> select * from my_table;

       ID MY_DATE

--------- ---------
        1 01-JAN-98
        2 05-FEB-00
        3 03-NOV-99
        4 06-OCT-00

SQL> update my_table
  2 set id = my_table_id.nextval
  3 where my_date in
  4 (select my_date from
  5 (select my_date from my_table order by my_date));

4 rows updated.

SQL> select * from my_table;

       ID MY_DATE

--------- ---------
        1 01-JAN-98
        3 05-FEB-00
        2 03-NOV-99
        4 06-OCT-00

If you need to do such a update periodically and want to always start with 1, you need to first drop sequence and recreate it again, as the numbers generated by sequence are unique, so that values already used would be "skipped"... So next update would be:

SQL> update my_table
  2 set id = my_table_id.nextval
  3 where my_date in
  4 (select my_date from
  5 (select my_date from my_table order by my_date));

4 rows updated.

SQL> select * from my_table;

       ID MY_DATE

--------- ---------
        5 01-JAN-98
        7 05-FEB-00
        6 03-NOV-99
        8 06-OCT-00

Have a nice day,

Erika

Francesco Marchioni wrote:
>
> Hello,
> I have the following problem: I have a table like:
>
> ID Date
> 1 1-Jan-1998
> 2 5-Feb-2000
> 3 3-Nov-1999
> 4 6-Oct-2000
>
> I'd need to sort the table ordered by Date but updating the ID so that it's
> still progressive. f.e.
>
> ID Date
> 1 1-Jan-1998
> 2 3-Nov-1999
> 3 5-Feb-2000
> 4 6-Oct-2000
>
> Is it possible to do it with a single update SQL statement ? or do I need a
> stored procedure to do it?
> (I'd be very grateful if I get some code that that show how to do it- I'm
> totally out of Oralce books
> at the moment).
> Thanks a lot
> Francesco
Received on Tue Aug 15 2000 - 03:29:25 CDT