Re: Update field from field in different table

From: <markp7832_at_my-deja.com>
Date: Thu, 17 Jun 1999 17:59:01 GMT
Message-ID: <7kbd0u$puk$1@nnrp1.deja.com>

In article <3769124b.1372080261_at_news1-alterdial.uu.net>, mred_at_qconline.com wrote:
> I have to update the vendor_name field in table A with values from the
> vendor_name field from table B, where the vendor_id fields from each
> table match.
> Something like:
> update tableA
> set tableA.vendor_name =
> tableB.vendor_name
> where tableA.vendor_id = tableB.vendor_id
> But this doesn't work. It returns an error saying tableB.vendor_id is
> an invalid field. What am I doing wrong? My guess is that this can't
> be done in SQL and I will have to use PL/SQL. Any syntax in either
> case would be greatly appreciated!
>

I do not believe that the where clause of an Update statement can contain a join. You can convert the query portion to be a coordinated subquery like:
set vendor_name = ( select vendor_name

                       from tableB
                      where tableB.vendor_id = tableA.vendor_id
                    )

I hope this helps.
--
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 17 1999 - 12:59:01 CDT