Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: UPDATE: Correlated Update Jump to:  
Category: >> Oracle PL/SQL >> UPDATE: Correlated Update Bookmark and Share

<< lastnext >>

Snippet Name: UPDATE: Correlated Update

Description: You can write a subquery that SQL may have to re-evaluate as it examines each new row (WHERE clause) or group of rows (HAVING clause) in the outer-level SELECT. This is called a correlated subquery.

When you use a correlated subquery in an UPDATE statement, the correlation name refers to the rows you are interested in updating.

Also see:
» UPDATE: Update a partitioned table
» UPDATE: Update based on a record
» UPDATE: Update Object Table
» UPDATE: with RETURNING clause
» UPDATE: Nested Table Update example
» UPDATE: Update from a SELECT statement
» UPDATE: based on multiple returned val...
» UPDATE: Update based on a query
» UPDATE: Update multiple rows
» UPDATE: update a specific record
» UPDATE: Single row
» UPDATE

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 04th, 2009

-- Single column example
 
UPDATE TABLE(<SELECT STATEMENT>) <alias>
SET <column_name> = (
  SELECT <column_name>
  FROM <table_name> <alias>
  WHERE <alias.table_name> = <alias.table_name>);
 
  UPDATE CORPDATA.PROJECT X
    SET PRIORITY = 1
    WHERE '1983-09-01' >
       (SELECT MAX(EMENDATE)
           FROM CORPDATA.EMPPROJACT
           WHERE PROJNO = X.PROJNO)
 
/*
As SQL examines each row in the CORPDATA.EMPPROJACT table, it 
determines the maximum activity end date (EMENDATE) for all 
activities of the project (from the CORPDATA.PROJECT table). 
If the end date of each activity associated with the project is 
prior to September 1983, the current row in the CORPDATA.PROJECT 
table qualifies and is updated.
 
Update the master order table with any changes to the quantity 
ordered. If the quantity in the orders table is not set (the NULL 
value), keep the value that is in the master order table.
*/
 
UPDATE MASTER_ORDERS X
   SET QTY=(SELECT COALESCE (Y.QTY, X.QTY)
              FROM ORDERS Y
              WHERE X.ORDER_NUM = Y.ORDER_NUM)
   WHERE X.ORDER_NUM IN (SELECT ORDER_NUM 
              FROM ORDERS)
 
 
-- Multiple column example
 
UPDATE <table_name> <alias>
SET (<column_name_list>) = (
  SELECT <column_name_list>
  FROM <table_name> <alias>
  WHERE <alias.table_name> <condition> <alias.table_name>);


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 279 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?