Solved

Joins in Update Statement

Posted on 2004-04-26
9
5,352 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle 10G 5 48
Remove Hyphens in Oracle SQL 5 45
querying by the sum of a column in decimal 7 45
Oracle 12c patching 1 33
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now