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

UPDATE T1
   SET T1.Field2 = (SELECT T2.Field2

                    FROM T2 
                    WHERE T2.Field1 = T1.Field1 
                    AND T2.Field3 = T1.Field3);

Regards
/Rauf Sarwar Received on Tue Dec 31 2002 - 05:27:28 CET