Solved

Joins in Update Statement

Posted on 2004-04-26
9
5,359 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
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.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PAYER_ID has both atributes 4 37
How to connect SQL Server from my Oracle database? 11 96
Distinct values from all columns in a table?? PL SQL 4 37
Help on model clause 5 31
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

776 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