Re: SQL: Update from multiple tables

"Okey Laboratory" <N/A> wrote in message news:<H7y8o5.9ws_at_campus-news-reading.utoronto.ca>...
> Hi all,
>
> I'm not sure if this is the right place for a SQL problem. If not, any
> advice on where to look would be very much appreciated.
>
> On Oracle 9i, I have two tables:
>
> T1(
> Field1 NOT NULL
> Field2
> Field3 NOT NULL)
>
> T2(
> Field1 NOT NULL
> Field2 NOT NULL
> Field3 NOT NULL
> )
>
> I would like to update T1.Field2 based on T2.Field2.
> That is, I really want something like:
>
> UPDATE T1, T2
> SET T1.Field2 = T2.Field2
> WHERE T1.Field1 = T2.Field1
> AND T1.Field3 = T2.Field3
>
> Of course I can't specify multiple tables in the UPDATE clause. I *SWEAR*
> I've solved this problem in a reasonably efficient manner in the past, but I
> can't seem to find a work-around now. Any suggestions?
>
> Tats

You could try the following ...
update (select T1.Field2 t1f2, T2.Field2 t2f2 from T1, T2

         where T1.Field1 = T2.Field2 and T1.Field3 = T2.Field3) TComb    set t1f2 = t2f2

Guy Received on Fri Jan 03 2003 - 18:07:27 CET