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

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 Mon Aug 07 2000 - 00:00:00 CDT