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
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
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
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It looks like it will work with just a unique index on Y.
create unique index idx_y on y(name)
create unique index idx_y on y(name)
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;
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.
Surrogate keys could help you get past this.
UPDATE X
SET x.Details = (SELECT y.Details FROM Y WHERE y.Name = x.name )
This should be a little faster than using cursors.