Link to home
Start Free TrialLog in
Avatar of bees
bees

asked on

Joins in Update Statement

I have to transfer an application from Access Database to Oracle database.

The Access database has an Update statement as follows:
UPDATE X INNER JOIN Y ON ( x.Name = y.Name )
SET x.Details = y.Details


I want a corresponding statement for Oracle database

The Best I could come up was
UPDATE X
SET x.Details = (SELECT y.Details FROM Y WHERE y.Name = x.name )
WHERE EXISTS( SELECT 1 FROM Y WHERE y.Name = x.name )

The details of the sam,ple tables are :
      Fields of Table X: Name, Details, Age
      Fields of Table Y: Name, Details, Criteria
     The Details of all records in X that has corresponding entry in Y has to be updated with the respective Details in Y.

Is there better solution for this UPDATE statement, without using cursors
Avatar of william_jwd
william_jwd
Flag of United States of America image

It is better to do this using a cursor since this query has two corellated sub-queries which will be slower when compared with the updation done using cursor.  If you are sure that all records in x ia also there in y then you can avoid the where condition.  

UPDATE X
SET x.Details = (SELECT y.Details FROM Y WHERE y.Name = x.name )

This should be a little faster than using cursors.
Avatar of bees
bees

ASKER

X is the master table and Y is the working table. So the data in Y has to be updated to respective records in X only.
Let's say that you have table X and table Y, and Y has a primary key on the join column name.

Then you can write an update very similar to the update you had in access:
/*
the following update joins the x and y table and then updates the name column in the x table to the name value in the y table.
*/

update (select x1.name as old_name,
                     y1.name as new_name
              from x1 inner join y1 on x1.name=y1.name)
set old_name=new_name
Do you have any record in x which is not there in Y.  If your answer to this is yes, then it is better to go with cursors rather than a single update statement.  If you have records in Y for all records in X, then you can go with the single update statement without the where condition.
Avatar of bees

ASKER

Based on comment from dbms_chu, I tried the following query :
UPDATE (SELECT x.details AS old_details,
                     y.details AS new_details
              FROM x INNER JOIN y ON x.NAME=y.NAME)
SET old_details=new_details
      ********
But the following error is shown at the *ed portion :
ORA-01779: cannot modify a column that maps to a non-key preserved column
ASKER CERTIFIED SOLUTION
Avatar of dbms_chu
dbms_chu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It looks like it will work with just a unique index on Y.

create unique index idx_y on y(name)
Avatar of bees

ASKER

yes. it did work on a primary key on y.name..

But for this statement to work, all the primary key columns in Y ( if primary key is of more than 1 column) should be there in the joining clause between these tables and equated to corresponding field in table X.

E.g. There is an extra field in the Y table dept_id that is part of Y_Pk but is not available in table X. Then the following query returns error ORA-01779
UPDATE (SELECT x.details AS old_details,
                     y.details AS new_details
              FROM x INNER JOIN y ON x.NAME=y.NAME and y.dept_id = 112)
SET old_details=new_details;
All true.

Surrogate keys could help you get past this.