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
beesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
william_jwdCommented:
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.
0
 
beesAuthor Commented:
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.
0
 
dbms_chuCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
william_jwdCommented:
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.
0
 
beesAuthor Commented:
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
0
 
dbms_chuCommented:
You need to create a primary key on the "y" table on column "name".
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
dbms_chuCommented:
It looks like it will work with just a unique index on Y.

create unique index idx_y on y(name)
0
 
beesAuthor Commented:
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;
0
 
dbms_chuCommented:
All true.

Surrogate keys could help you get past this.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.