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.
Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 04th, 2009
-- Single column exampleUPDATETABLE(<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 =1WHERE'1983-09-01'>(SELECTMAX(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=(SELECTCOALESCE(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 exampleUPDATE<table_name><alias>SET(<column_name_list>)=(SELECT<column_name_list>FROM<table_name><alias>WHERE<alias.table_name><condition><alias.table_name>);
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.