Solved

Correlated subquery in an update statement?

Posted on 1998-11-02
1
1,024 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 50 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

831 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