Re: UPDATE with 2 tables

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/02
Message-ID: <8h95lp$89l$1_at_nnrp1.deja.com>#1/1

In article <8h90rm$13s$1_at_news6.isdnet.net>,   "Stéphane lemoine" <lemoines_at_cybercable.fr> wrote:
> I have a problem
>
> there are 2 table A and B
> a (IDA, FA)
> b (IDB, FB, FKA)
> a is linked to b with IDA = FKA
>
> i want to update FA with the value of FB
> IDA = FKA
>
> i dont know how to write the SQL statement
>
> UPDATE a
> SET FA
> =(select...)
>
> please help me
>
>

2 ways -- second is better/faster but FKA must be defined as UNIQUE (if it is not unique, the second will work "syntactically" but will fail at runtime since a query by FKA will return >1 record!! therefore in all cases, FKA must be unique!!)

ops$tkyte_at_DEV8I.WORLD> create table a ( ida int, fa int );

Table created.

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> create table b ( idb int, fb int, fka int );

Table created.

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> insert into a values ( 1, 1 );

1 row created.

ops$tkyte_at_DEV8I.WORLD> insert into b values ( 1, 2, 1 );

1 row created.

ops$tkyte_at_DEV8I.WORLD> commit;

Commit complete.

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> update a

  2 set fa = ( select fb from b where b.fka = a.ida )   3 where exists ( select fb from b where b.fka = a.ida )   4 /

1 row updated.

ops$tkyte_at_DEV8I.WORLD> select * from a;

       IDA FA
---------- ----------

         1 2

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> rollback;

Rollback complete.

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> ALTER TABLE B

  2 add constraint
  3 FKA_MUST_BE_UNIQUE_FOR_THIS
  4 UNIQUE(fka);

Table altered.

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> update
  2    ( select fa, fb
  3            from a, b
  4           where ida = fka )
  5     set fa = fb

  6 /

1 row updated.

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> select * from a;

       IDA FA
---------- ----------

         1 2

ops$tkyte_at_DEV8I.WORLD>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Jun 02 2000 - 00:00:00 CEST