Re: A tricky update...

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 6 Jun 2001 17:02:56 +0200
Message-ID: <9flgn1$7dv$1@s1.read.news.oleane.net>

You can add a column to your table
then create a sequence and a trigger to populate it and finaly make it a primary key.

Example:

v815> create table t (col number);
v815> insert into t values (0);
v815> /
v815> commit;
v815> select * from t;

       COL
----------
         0
         0

2 ligne(s) sélectionnée(s).

v815> alter table t add (id number);

Table modifiée.

v815> create sequence t_s;

Séquence créée.

v815> create or replace trigger t_t
  2 before insert or update on t
  3 for each row
  4 begin

  5     if inserting then
  6        select t_s.nextval into :new.id from dual;
  7     else
  8        if :old.id is null then
  9           select t_s.nextval into :new.id from dual;
 10        end if;
 11     end if;

 12 end;
 13 /

Déclencheur créé.

v815> select * from t;

       COL ID
---------- ----------

         0
         0

2 ligne(s) sélectionnée(s).

v815> update t set col=col;

2 ligne(s) mise(s) à jour.

v815> select * from t;

       COL ID
---------- ----------

         0          1
         0          2

2 ligne(s) sélectionnée(s).

v815> insert into t (col) values (1);

1 ligne créée.

v815> select * from t;

       COL ID
---------- ----------

         0          1
         0          2
         1          3

3 ligne(s) sélectionnée(s).

v815> alter table t add constraint pk_t primary key (id);

Table modifiée.

--
Hope this helps
Michel


"Benetnasch" <benetnasch_at_blimey.co.uk> a écrit dans le message news:
5c5c8561.0106060646.15f1035_at_posting.google.com...


> I have 2 rows in a table with exactly the information

> in them (badly designed, I know, but it's a legacy

> system). I need to update these rows so that a blank

> column is assigned a unique identifier. How can I

> run an SQL update on them to achieve this, please?

>

> Thanks for any information,

>

> Mark

Received on Wed Jun 06 2001 - 10:02:56 CDT