Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Joins in Update Statement

Posted on 2004-04-26
9
Medium Priority
?
5,364 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:bees
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 8

Expert Comment

by:william_jwd
ID: 10916252
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
 

Author Comment

by:bees
ID: 10916272
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
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10916326
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 8

Expert Comment

by:william_jwd
ID: 10916338
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
 

Author Comment

by:bees
ID: 10916518
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
 
LVL 3

Accepted Solution

by:
dbms_chu earned 750 total points
ID: 10916675
You need to create a primary key on the "y" table on column "name".
0
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10916706
It looks like it will work with just a unique index on Y.

create unique index idx_y on y(name)
0
 

Author Comment

by:bees
ID: 10916948
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
 
LVL 3

Expert Comment

by:dbms_chu
ID: 10917069
All true.

Surrogate keys could help you get past this.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question