SQL Update Query

From: <ksimon_at_omnisource.com>
Date: Mon, 06 Jul 1998 14:51:58 GMT
Message-ID: <35a0e404.5604907_at_news.fwi.com>

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)

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 Received on Mon Jul 06 1998 - 16:51:58 CEST