Joins in Update Statement
Posted on 2004-04-26
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
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