Re: Problem With Updates Using Joins

In article <20000825134523.27693.00000371_at_ng-bk1.aol.com>,   mander2686_at_aol.com (MAnder2686) wrote:
> For years, using Sybase or MS SQL Server, I have been able to write a
 query
> that uses a join to update a table such as this:
>
> UPDATE EMPLOYEE_NEW
> SET A.SALARY = B.SALARY
> FROM EMPLOYEE_NEW A, EMPLOYEE_OLD B
> WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID
>
> I had always assumed this was part of standard SQL.
>
> However, Oracle 8i give me an error stating that I have not properly
> termintated the SQL statement. I asked my DBA and he told me I had
 to re-write
> my query with a correlated sub query:
>
> UPDATE EMPLOYEE_NEW A
> SET A.SALARY = ( SELECT B.SALARY
> FROM EMPLOYEE_OLD B
> WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID )
>
> Is this the only way to do this type of update in Oracle? It seems
 rather
> awkward compared to what I had been doing.
>
> Thank you,
>
> Michael A. Anderson
>

As you have learned Oracle does not support join sub-queries as input to the set statement on an update. You have to re-write the join as a nonjoin subquery using coordinated subqueries or in clauses etc...

By the way the sql statement above will set the salary to null where the table A row does not have a match in B. You will not see this if every row in A has a match in B but if A has new entries not in B you just made somebody unhappy come pay day. You need a where clause condition to only update A if A exists in B.

where exists ( SELECT c.SALARY

               FROM EMPLOYEE_OLD c
               WHERE c.EMPLOYEE_ID = a.EMPLOYEE_ID )

--
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/
Before you buy.