Solved

Correlated subquery in an update statement?

Posted on 1998-11-02
1
1,008 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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now