?
Solved

Correlated subquery in an update statement?

Posted on 1998-11-02
1
Medium Priority
?
1,032 Views
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.....)
0
Comment
Question by:edwardk091997
1 Comment
 
LVL 2

Accepted Solution

by:
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.

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your 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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

750 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