Update based upon a select
I need to update Table_1 based upon a select of data from two other tables "ROLD" and "PP";
My Oracle Sql is not correct. Can someone tell me how to modify the query below so that it is syntactically correct?
UPDATE Table_1
SET
(Table_1.Id = ROLD.Id,
Table_1.FName = ROLD.FName,
Table_1.LName = ROLD.LNAME,
Table_1.PurchasePrice = PP.PurchasePrice
) =
(FROM ROLD,PP WHERE ROLD.ID = PP.ID);
My Oracle Sql is not correct. Can someone tell me how to modify the query below so that it is syntactically correct?
UPDATE Table_1
SET
(Table_1.Id = ROLD.Id,
Table_1.FName = ROLD.FName,
Table_1.LName = ROLD.LNAME,
Table_1.PurchasePrice = PP.PurchasePrice
) =
(FROM ROLD,PP WHERE ROLD.ID = PP.ID);
Pawan Kumar
How Table_1 and the ( SELECT of ROLD,PP ) are related.. how they are joined.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>instead of the merge syntax, you can use this syntax:
Using my test case above, small syntax error with "SET UPDATE Table_1"
>>or a sub-query of ROLD (and/or PP) that will return only the matching rows.
That is why I didn't post that syntax. It would normally be another query against the base table. I'm not sure it is worth the extra table access but I suppose it might be better in certain situations.
Execution plans would likely decide it.
Using my test case above, small syntax error with "SET UPDATE Table_1"
>>or a sub-query of ROLD (and/or PP) that will return only the matching rows.
That is why I didn't post that syntax. It would normally be another query against the base table. I'm not sure it is worth the extra table access but I suppose it might be better in certain situations.
Execution plans would likely decide it.
>>I agree that MERGE is probably what you want here. Pawan's code has a couple of errors in it.
There are no errors in my code. Slightwv has just used my code(only 1 change Table_1 -> tab1) with an added condition ON (T.id = l.id) and removed this from the update statement. It may or may not be correct so let the author confirm that. The joining condition was not clear - Read my first comment - "How Table_1 and the ( SELECT of ROLD,PP ) are related.. how they are joined.".
There are no errors in my code. Slightwv has just used my code(only 1 change Table_1 -> tab1) with an added condition ON (T.id = l.id) and removed this from the update statement. It may or may not be correct so let the author confirm that. The joining condition was not clear - Read my first comment - "How Table_1 and the ( SELECT of ROLD,PP ) are related.. how they are joined.".
>>There are no errors in my code.
There are more changes than just the table name. Please run my test case using your SQL. It will generate syntax errors.
There are more changes than just the table name. Please run my test case using your SQL. It will generate syntax errors.