Home » SQL & PL/SQL » SQL & PL/SQL » Update statement help (Oracle 10g)
icon9.gif  Update statement help [message #433014] Sat, 28 November 2009 14:52 Go to next message
bnath001
Messages: 10
Registered: September 2006
Junior Member
Hello folks,

I need some help/advise on an UPDATE statement.

I have two tables. I need to join these tables and update a field in table1 with a field from table2.

Please see the attached jpg file.

The first 3 coloumns are from table1 and last 2 columns in the picture are from table2.

I want to update NEXT_RATE_RESET_ADJ_P field (from table1) with NEXT_RESET_DATE_ADJUSTED field (from table2). I want to join them between table1.DEAL_LEG_ID_P and table2.DEAL_LEG_ID.

I have never written update statement with a join in it.
Any help is greatly appreciated.

./fa/7084/0/
Re: Update statement help [message #433015 is a reply to message #433014] Sat, 28 November 2009 15:00 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
UPDATE statement Examples

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)


Alternatively,
UPDATE T1 SET COL1 =
nvl((SELECT T2.COL1 FROM T2,T1 WHERE T2.ID = T1.ID),COL1)


[Updated on: Sat, 28 November 2009 15:21]

Report message to a moderator

Re: Update statement help [message #433019 is a reply to message #433015] Sun, 29 November 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The alternative is bad, why would you update ALL rows when only part of them need to?
Think, you have to update 10 rows and you udpate 1000000, for instance!

Regards
Michel
Re: Update statement help [message #433020 is a reply to message #433015] Sun, 29 November 2009 01:37 Go to previous messageGo to next message
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 #433022 is a reply to message #433020] Sun, 29 November 2009 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the col1 to the old value instead of NULL when no match is found.

Your sentence is misleading, it may lead to think the first one does it when it does not.

Quote:
For small tables, the difference is irrelevent

I disagree.
NEVER do something that it is not needed to do.
You update what it must not to be updated.
You generate more undo and redo => contention
You lock rows => contention
You use more CPU => contention
You use more write => contention (on IO subsystem and/or cache)
And so on (it is worse in RAC sstem)

Doing unecessary work does not impact only you, it impacts everyone in the system.

Regards
Michel

(NEVER means almost never, ALWAYS means almost always)
Re: Update statement help [message #433025 is a reply to message #433022] Sun, 29 November 2009 02:26 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks Sir for the suggesion.

This link was misleading...
http://www.orafaq.com/forum/t/7547/2/
Re: Update statement help [message #433026 is a reply to message #433022] Sun, 29 November 2009 02:46 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Update statement help [message #433027 is a reply to message #433026] Sun, 29 November 2009 02:52 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks Sir ...
Doesn't work - Re: Update statement help [message #433195 is a reply to message #433015] Mon, 30 November 2009 10:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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)
icon14.gif  Re: Update statement help [message #433202 is a reply to message #433014] Mon, 30 November 2009 11:27 Go to previous messageGo to next message
bnath001
Messages: 10
Registered: September 2006
Junior Member
This is the 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)
Re: Update statement help [message #433203 is a reply to message #433202] Mon, 30 November 2009 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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 Go to previous message
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;
Previous Topic: Dynamically populating variables
Next Topic: strange problem
Goto Forum:
  


Current Time: Fri Sep 30 03:47:24 CDT 2016

Total time taken to generate the page: 0.10955 seconds