Re: SQL Update Query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 06 Jul 1998 16:04:35 GMT
Message-ID: <35a5f591.11429014_at_192.86.155.100>

A copy of this was sent to ksimon_at_omnisource.com (if that email address didn't require changing) On Mon, 06 Jul 1998 14:51:58 GMT, you wrote:

>I am attempting to update two fields on one table from two fields on
>another table, but am unsure of the correct syntax. Example:
>
>Table MASTER
> Fields: ID
> Company
> Division
>
>
>Table TRANSACTIONS
> Fields: ID
> Company
> Division
>
> The ID field on TRANSACTIONS is a foreign key, the ID field on MASTER
>is the primary key. For those who are wondering, I am updating the
>Company and Division from a number of sources, which is why I am
>duplicating the data on the TRANSACTIONS table.
>
>Anyway, I want to write a query which updates the Company and Division
>fields on TRANSACTIONS from MASTER where the ID is equal. I am using
>MS Access to prototype a system, and the syntax there is:
> UPDATE transactions
> INNER JOIN master ON transactions.id = master.id
> SET transactions.company = master.company,
> transactions.division = master.division
>
>The problem is I cannot seem to duplicate this logic with standard
>Oracle 7.3 SQL. The closest I have come is:
> UPDATE transactions
> SET company = ( SELECT company FROM master
> WHERE master.id = transaction.id),
> division = (SELECT division FROM master
> WHERE master.id = transaction.id)
>

it would be:

SQL> update transactions

  2     set (company,division) = ( select company, division
  3                                  from master
  4                                 where master.id = transactions.id )

  5 where id in ( select id from master )   6 /

>The problem with this is obvious: I am performing essentially the
>same subquery twice. With a large amount of data, this is too much
>overhead.
>
>
>Any help?
>
>Thanks much.
>
>ksimon_at_omnisource.com
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jul 06 1998 - 18:04:35 CEST