|
|
|
Re: Update statement help [message #433020 is a reply to message #433015] |
Sun, 29 November 2009 01:37   |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Option 2 does update each row but sets the col1 to the old value instead of NULL when no match is found. This "fake" update may not be desirable in certain situations (rollback size, triggers, etc.), but it does avoid the dual lookup.
For small tables, the difference is irrelevent
|
|
|
|
|
Re: Update statement help [message #433026 is a reply to message #433022] |
Sun, 29 November 2009 02:46   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
In addition to Michel's list of why not update if not needed:
If a table has audit triggers on it, you would generate an audit record (or update the last_update_xxx columns). This might be very unwanted.
|
|
|
|
Doesn't work - Re: Update statement help [message #433195 is a reply to message #433015] |
Mon, 30 November 2009 10:36   |
bnath001
Messages: 10 Registered: September 2006
|
Junior Member |
|
|
I get the following error message.
Error starting at line 1 in command:
update qrm_swaps_curr_eom
set qrm_swaps_curr_eom.next_rate_reset_date_adj_p = (select next_reset_date.next_reset_date_adjusted
from qrm_swaps_curr_eom, next_reset_date
where next_reset_date.deal_leg_id= qrm_swaps_curr_eom.deal_leg_id_p)
where exists (select null from next_reset_date, qrm_swaps_curr_eom where next_reset_date.deal_leg_id= qrm_swaps_curr_eom.deal_leg_id_p)
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:
|
|
|
Re: Update statement help [message #433196 is a reply to message #433014] |
Mon, 30 November 2009 10:46   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Can you please use code tags when posting code, it makes it more readable - see the orafaq forum guide if you're not sure how.
The error message is fairly self explanatory - the first sub query (in the set) returns mutliple rows. Oracle can't know which row to use to update the table so raises an error.
Question: why is qrm_swaps_curr_eom in the From of the sub-query?
I suspect it shouldn't be there as you should be querying next_reset_date based on the deal_leg_id_p of the record you're trying to update. Which it would do if you just removed qrm_swaps_curr_eom from the sub-query.
I suspect it shouldn't be in the other sub-query either.
|
|
|
Re: Update statement help [message #433197 is a reply to message #433014] |
Mon, 30 November 2009 10:50   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Looking back up the thread this example:
UPDATE T1 SET T1.COL1 =
(SELECT T2.COL1 FROM T2,T1 WHERE T2.ID = T1.ID)
WHERE EXISTS (SELECT NULL FROM T2,T1 WHERE T2.ID = T1.ID)
Is wrong, it's supposed to be 2 correlated sub-queries but since the table to be updated is declared in the from of both sub-queries it isn't correlated.
The correct version should be:
UPDATE T1 SET T1.COL1 =
(SELECT T2.COL1 FROM T2 WHERE T2.ID = T1.ID)
WHERE EXISTS (SELECT NULL FROM T2 WHERE T2.ID = T1.ID)
|
|
|
|
Re: Update statement help [message #433203 is a reply to message #433202] |
Mon, 30 November 2009 11:38   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
This is formatted query that works
UPDATE qrm_swaps_curr_eom
SET qrm_swaps_curr_eom.next_rate_reset_date_adj_r = (SELECT next_reset_date.next_reset_date_adjusted
FROM next_reset_date
WHERE qrm_swaps_curr_eom.deal_leg_id_r = next_reset_date.deal_leg_id)
WHERE qrm_swaps_curr_eom.deal_leg_id_r IN (SELECT next_reset_date.deal_leg_id
FROM next_reset_date)
[Updated on: Mon, 30 November 2009 11:38] Report message to a moderator
|
|
|
Re: Update statement help [message #433281 is a reply to message #433203] |
Tue, 01 December 2009 03:42  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you could use MERGE, dropping the number of times you need to query the NEXT_RESET_DATE table, and allowing you to use a Hash Join:MERGE INTO qrm_swaps_curr_eom tgt
USING (SELECT n.next_reset_date_adjusted
,n.deal_leg_id
FROM next_reset_date n) src
ON (tgt.deal_leg_id_r = src.deal_leg_id)
WHEN MATCHED THEN UPDATE SET tgt.next_rate_reset_date_adj_r = src.next_reset_date_adjusted;
|
|
|