Re: Updating a column's contents to the contents of another table's column, where both tables are linked by an ID

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/08/08
Message-ID: <8mod25$son$1@s1.read.news.oleane.net>#1/1

If ID are primary keys, I prefer that one:

update (select table1.color, table2.color

        from table2, table1
        where table2.id = table1.id)

set table1.color = table2.color
/

--
Have a nice day
Michel


Sybrand Bakker <postbus_at_sybrandb.demon.nl> a écrit dans le message :
965680458.10611.0.pluto.d4ee154e_at_news.demon.nl...


> IMO you should try to learn SQL before *pl/sql* which is an extension *only*

> not a replacement

>

> update table1

> set color =

> (select color

>   from table2

>   where table2.id = table1.id

> )

> where table1.id

> in

> (select id from table2)

>

> Hth,

>

> Sybrand Bakker, Oracle DBA

>

> "Ken Ho" <hoke_at_gse.harvard.edu> wrote in message

> news:398f12fb.19151402_at_news.harvard.edu...

> > How do you update a column's contents to the contents of another

> > table's column, where both tables are linked by an ID?

> >

> > E.g.:

> >

> > TABLE1.ID

> > TABLE1.COLOR

> >

> > TABLE2.ID

> > TABLE2.COLOR

> >

> > and I want to set TABLE1.COLOR to be the same value as TABLE2.COLOR

> > whenever TABLE1.ID = TABLE2.ID?

> >

> > I realize that in many or all cases this will mean the data is not

> > normalized, but is there another way (e.g., using just SQL) to do this

> > besides the following PL/SQL code:

> >

> > CURSOR TABLE2_COLORS_CUR IS

> > SELECT T1.ROWID, T2.*

> > FROM TABLE1 T1, TABLE2 T2

> > WHERE T1.ID = T2.ID;

> >

> > FOR rec IN TABLE2_COLORS_CUR

> > LOOP

> >   update TABLE1

> > set COLOR = rec.COLOR

> > where rowid=REC.rowid

> > END LOOP

>

>

Received on Tue Aug 08 2000 - 00:00:00 CDT