> Need help with an UPDATE statement involving multiple tables in Oracle8.
> Here it is:
> UPDATE employees e, address a SET e.division ='North America'
> where e.employee_id = a.employee_id
> and a.country ='USA'
> 
> I am experiencing problems with having multiple tables in the UPDATE clause.
> In my actual query, I have many more tables with aliases in the UPDATE clause
> table-specification.
> 
> Will appreciate any help.
> 
> Thanks.
Vanita,

You can only UPDATE one table. So, you can change your SQL to the following:

UPDATE employees e
SET e.division = 'North America'
WHERE exists
     (SELECT a.employee_id
      FROM address a
      WHERE a.country='USA' AND
            a.employee_id = e.employee_id)
/

This will have the same effect as your SQL, but will work with Oracle.

Best regards,


-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 265+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page > Back to Ari Kaplan's Home Page