From: "Michel Cadot" <micadot@netcourrier.com>
Subject: Re: Updating a column's contents to the contents of another table's column, where both tables are linked by an ID
Date: 2000/08/08
Message-ID: <8mod25$son$1@s1.read.news.oleane.net>#1/1
References: <398f12fb.19151402@news.harvard.edu> <965680458.10611.0.pluto.d4ee154e@news.demon.nl>
X-Priority: 3
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
X-Complaints-To: abuse@oleane.net
X-Trace: s1.read.news.oleane.net 965719941 29463 195.101.229.231 (8 Aug 2000 07:32:21 GMT)
Organization: Guest of France Telecom Oleane's newsreading service
X-MSMail-Priority: Normal
Reply-To: "Michel Cadot" <micadot@netcourrier.com>
NNTP-Posting-Date: Tue, 8 Aug 2000 07:32:21 +0000 (UTC)
Newsgroups: comp.databases.oracle.misc


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@sybrandb.demon.nl> a écrit dans le message :
965680458.10611.0.pluto.d4ee154e@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@gse.harvard.edu> wrote in message
> news:398f12fb.19151402@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
>
>