Correlated subquery in an update statement?

Posted on 1998-11-02
Medium Priority
Last Modified: 2008-03-17
I have used correlated sub-queries in select statements, -- and I have used embedded selects in update statements, BUT can you you use correlated sub-queries with update statements?

something like:

UPDATE carts SET carts.format_code = prices.format_code
WHERE cart_item_id IN
      (SELECT cart_item_id from prices, carts
       WHERE  blah blah blah.....)
Question by:edwardk091997
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment

Accepted Solution

avico earned 200 total points
ID: 1082395
the above update is incorrect :

UPDATE carts SET carts.format_code = prices.format_code
WHERE cart_item_id IN
      (SELECT cart_item_id from prices, carts
       WHERE  blah blah blah.....)

the 'prices' table is unknown to the update statement, and only exists in the sub-select.

However, you can issue the following update commnad in order to get the requested affect :
UPDATE carts c, prices p
SET c.format_code = p.format_code
WHERE c.cart_item_id IN
      (SELECT t.cart_item_id from prices r, carts t
       WHERE  r.<price_table_key> = p.<price_table_key>
       AND    blah blah blah.....)

Hope it answered your question.


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question