Home » SQL & PL/SQL » SQL & PL/SQL » Help on an Update statement for select records (Oracle 9.2.0.1 SQL)
Help on an Update statement for select records [message #383861] Fri, 30 January 2009 15:43 Go to next message
mjchevalier
Messages: 2
Registered: January 2009
Junior Member
I am in an implementation of a new software system and have to update an Oracle file using sql. Please help me with the code. I need to update all records in CUSTITEM table with the QTY value from the CUSTITEMUOM table where the ITEM from the CUSTITEM table = the ITEM from the CUSTITEMUOM table and where the SEQUENCE value from the CUSTITEMUOM table = 10 and where the FROMUOM value from the CUSTITEMUOM table = 'PCS'. Is this clear? Can someone help me? I understand a basic UPDATE statement but not one based on values from another table. Thank you very much!
Re: Help on an Update statement for select records [message #383892 is a reply to message #383861] Sat, 31 January 2009 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post DDL. Also always post your Oracle version (4 decimals).
Have a look at MERGE statement.

Regards
Michel
Re: Help on an Update statement for select records [message #383898 is a reply to message #383861] Sat, 31 January 2009 00:43 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your words literally translated into SQL:
update custitem c set
  c.qty = (select i.qty
           from custitemuom i
           where i.item = c.item
             and i.sequence = 10
             and i.fromuom = 'PCS'
          );

Perhaps you'd want to learn how to produce a solution by reading more about the UPDATE statement (note an example at the bottom of the page).
Re: Help on an Update statement for select records [message #384176 is a reply to message #383898] Mon, 02 February 2009 09:45 Go to previous messageGo to next message
mjchevalier
Messages: 2
Registered: January 2009
Junior Member
Thanks Littlefoot! That worked and I understand the statement also. One more thing, can you recommend a good book to begin learning sql with Oracle as I will be needing to learn more in the future? I appreciate your assistance. Thank you.
Re: Help on an Update statement for select records [message #384178 is a reply to message #384176] Mon, 02 February 2009 10:05 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you are interesting in syntax and options, you could read SQL Reference

Regards
Michel
Previous Topic: outer join with where clause
Next Topic: Reading into a Defined structure (merged)
Goto Forum:
  


Current Time: Fri Feb 07 15:59:29 CST 2025