Home » SQL & PL/SQL » SQL & PL/SQL » Beginner question. Update query with another SQL (11g)
Beginner question. Update query with another SQL [message #683985] Sat, 13 March 2021 12:54 Go to next message
Messages: 11
Registered: December 2013
Location: New York City
Junior Member
I am using 11g.

I have an SQL statement that returns 2 columns. A customer ID ( it is a Primary Key ) and cash amount.

I need to update a table with the above SQL statement. Why? Because I only need to change the cash for certain customers that meet the criteria. Not all customers.

How do I do an update Set query with an SQL statement.

Re: Beginner question. Update query with another SQL [message #683986 is a reply to message #683985] Sat, 13 March 2021 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68657
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Wed, 02 July 2014 01:22
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Michel Cadot wrote on Fri, 29 May 2020 20:51

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

In the end, feedback to your topics if you want further help.
Re: Beginner question. Update query with another SQL [message #683987 is a reply to message #683986] Sun, 14 March 2021 06:10 Go to previous message
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't provide much information.

Anyway, MERGE might do the job.

merge into another_table a
  using (select b.customer_id,
         from some_table b
         where certain_condition
        ) x
  on (x.customer_id = a.customer_id)
  when matched then update set
    a.cash_amount = x.cash_amount;
Why MERGE and not UPDATE? No particular reason, apart from the fact that MERGE takes care about which rows to update, while in UPDATE you have to do it yourself, either by using EXISTS or IN.
Previous Topic: Parsing XML multiple CDATA with & and withoutn &
Next Topic: Generating Installment rows
Goto Forum:

Current Time: Fri May 24 12:42:00 CDT 2024