Oracle Update Statement With Subquery
Purpose
- Oracle Update Statement With Subquery Number
- Oracle Update Statement Using Subquery
- Oracle Update Statement Example
A subquery is a SQL query nested inside a larger query. A subquery may occur in: - A SELECT clause - A FROM clause - A WHERE clause; The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
- SQL update using subqueries with 'IN' In the following we are going to discuss the usage of IN within a subquery with the UPDATE statement, to update the specified columns. Example: Sample table: orders.
- For starters, a subquery is a SELECT statement that is included or nested within another SQL statement, which can be another SELECT or an INSERT, UPDATE or DELETE.
Use the UPDATE
statement to change existing values in a table or in the base table of a view or the master table of a materialized view.
Additional Topics
Prerequisites
For you to update values in a table, the table must be in your own schema or you must have the UPDATE
object privilege on the table.
For you to update values in the base table of a view:
You must have the
UPDATE
object privilege on the view, andWhoever owns the schema containing the view must have the
UPDATE
object privilege on the base table.
The UPDATE
ANY
TABLE
system privilege also allows you to update values in any table or in the base table of any view.
You must also have the SELECT
object privilege on the object you want to update if:
The object is on a remote database or
The
SQL92_SECURITY
initialization parameter is set toTRUE
and theUPDATE
operation references table columns, such as the columns in awhere_clause
.
Syntax
update::=
Description of the illustration 'update.gif'
(DML_table_expression_clause::=, update_set_clause ::=, where_clause ::=, returning_clause::=, error_logging_clause::=)
DML_table_expression_clause::=
Description of the illustration 'dml_table_expression_clause.gif'
(partition_extension_clause::=, subquery::=--part of SELECT
, subquery_restriction_clause ::=, table_collection_expression ::=)
partition_extension_clause::=
Description of the illustration 'partition_extension_clause.gif'
subquery_restriction_clause ::=
Description of the illustration 'subquery_restriction_clause.gif'
table_collection_expression ::=
Description of the illustration 'table_collection_expression.gif'
update_set_clause ::=
Description of the illustration 'update_set_clause.gif'
where_clause ::=
Description of the illustration 'where_clause.gif'
returning_clause::=
Description of the illustration 'returning_clause.gif'
error_logging_clause::=
Description of the illustration 'error_logging_clause.gif'
Semantics
hint
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
You can place a parallel hint immediately after the UPDATE
keyword to parallelize both the underlying scan and UPDATE
operations.
See Also:
'Hints' for the syntax and description of hints
Oracle Database Concepts for detailed information about parallel execution
DML_table_expression_clause
The ONLY
clause applies only to views. Specify ONLY
syntax if the view in the UPDATE
clause is a view that belongs to a hierarchy and you do not want to update rows from any of its subviews.
See Also:
'Restrictions on the DML_table_expression_clause' and 'Updating a Table: Examples'schema
Specify the schema containing the object to be updated. If you omit schema
, then the database assumes the object is in your own schema.
tableviewmaterialized_viewsubquery
Specify the name of the table, view, materialized view, or the columns returned by a subquery to be updated. Issuing an UPDATE
statement against a table fires any UPDATE
triggers associated with the table.
If you specify
view
, then the database updates the base table of the view. You cannot update a view except withINSTEAD
OF
triggers if the defining query of the view contains one of the following constructs:- A set operator
- A
DISTINCT
operator - An aggregate or analytic function
- A
GROUP
BY
,ORDER
BY
,MODEL
,CONNECT
BY
, orSTART
WITH
clause - A collection expression in a
SELECT
list - A subquery in a
SELECT
list - A subquery designated
WITH
READ
ONLY
- A recursive
WITH
clause - Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
You cannot update more than one base table through a view.
In addition, if the view was created with the
WITH
CHECK
OPTION
, then you can update the view only if the resulting data satisfies the view's defining query.If
table
or the base table ofview
contains one or more domain index columns, then this statement executes the appropriate indextype update routine.You cannot update rows in a read-only materialized view. If you update rows in a writable materialized view, then the database updates the rows from the underlying container table. However, the updates are overwritten at the next refresh operation. If you update rows in an updatable materialized view that is part of a materialized view group, then the database also updates the corresponding rows in the master table.
See Also:
Oracle Database Data Cartridge Developer's Guide for more information on the indextype update routines
CREATE MATERIALIZED VIEW for information on creating updatable materialized views
partition_extension_clause
Specify the name or partition key value of the partition or subpartition within table
targeted for updates. You need not specify the partition name when updating values in a partitioned table. However in some cases specifying the partition name can be more efficient than a complicated where_clause
.
See Also:
'References to Partitioned Tables and Indexes' and 'Updating a Partition: Example'dblink
Specify a complete or partial name of a database link to a remote database where the object is located. You can use a database link to update a remote object only if you are using Oracle Database distributed functionality.
If you omit dblink,
then the database assumes the object is on the local database.
See Also:
'References to Objects in Remote Databases' for information on referring to database linkssubquery_restriction_clause
Use the subquery_restriction_clause
to restrict the subquery in one of the following ways:
WITH READ ONLY Specify WITH READ ONLY
to indicate that the table or view cannot be updated.
WITH CHECK OPTION Specify WITH CHECK OPTION
to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM
clause but not in subquery in the WHERE
clause.
CONSTRAINT constraintSpecify the name of the CHECK OPTION
constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form SYS_C
n
, where n is an integer that makes the constraint name unique within the database.
See Also:
Oracle Update Statement With Subquery Number
'Using the WITH CHECK OPTION Clause: Example'table_collection_expression
The table_collection_expression
lets you inform Oracle that the value of collection_expression
should be treated as a table for purposes of query and DML operations. The collection_expression
can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.
The optional plus (+) is relevant if you are joining the TABLE
collection expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.
Note:
In earlier releases of Oracle, whencollection_expression
was a subquery, table_collection_expression
was expressed as THE
subquery
. That usage is now deprecated.You can use a table_collection_expression
to update rows in one table based on rows from another table. For example, you could roll up four quarterly sales tables into a yearly sales table.
t_alias
Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement. This alias is required if the DML_table_expression_clause
references any object type attributes or object type methods.
Restrictions on the DML_table_expression_clauseThis clause is subject to the following restrictions:
You cannot execute this statement if
table
or the base table ofview
contains any domain indexes markedIN_PROGRESS
orFAILED
.You cannot insert into a partition if any affected index partitions are marked
UNUSABLE
.You cannot specify the
order_by_clause
in the subquery of theDML_table_expression_clause
.If you specify an index, index partition, or index subpartition that has been marked
UNUSABLE
, then theUPDATE
statement will fail unless theSKIP_UNUSABLE_INDEXES
session parameter has been set toTRUE
.
See Also:
ALTER SESSION for information on theSKIP_UNUSABLE_INDEXES
session parameterupdate_set_clause
The update_set_clause
lets you set column values.
column
Specify the name of a column of the object that is to be updated. If you omit a column of the table from the update_set_clause
, then the value of that column remains unchanged.
If column
refers to a LOB object attribute, then you must first initialize it with a value of empty or null. You cannot update it with a literal. Also, if you are updating a LOB value using some method other than a direct UPDATE
SQL statement, then you must first lock the row containing the LOB. See for_update_clause for more information.
If column
is a virtual column, you cannot specify it here. Rather, you must update the values from which the virtual column is derived.
If column
is part of the partitioning key of a partitioned table, then UPDATE
will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement. Refer to the row_movement_clause
of CREATE TABLE or ALTER TABLE.
In addition, if column
is part of the partitioning key of a list-partitioned table, then UPDATE
will fail if you specify a value for the column that does not already exist in the partition_key_value
list of one of the partitions.
subquery
Specify a subquery that returns exactly one row for each row updated.
If you specify only one column in the
update_set_clause
, then the subquery can return only one value.If you specify multiple columns in the
update_set_clause
, then the subquery must return as many values as you have specified columns.If the subquery returns no rows, then the column is assigned a null.
If this
subquery
refers to remote objects, then theUPDATE
operation can run in parallel as long as the reference does not loop back to an object on the local database. However, if thesubquery
in theDML_table_expression_clause
refers to any remote objects, then theUPDATE
operation will run serially without notification.
You can use the flashback_query_clause
within the subquery to update table
with past data. Refer to the flashback_query_clause of SELECT
for more information on this clause.
See Also:
SELECT and 'Using Subqueries'
parallel_clause in the CREATE TABLE documentation
expr
Specify an expression that resolves to the new value assigned to the corresponding column.
See Also:
Chapter 6, 'Expressions' for the syntax ofexpr
and 'Updating an Object Table: Example'DEFAULT Specify DEFAULT
to set the column to the value previously specified as the default value for the column. If no default value for the corresponding column has been specified, then the database sets the column to null.
Restriction on Updating to Default Values You cannot specify DEFAULT
if you are updating a view.
VALUE Clause
The VALUE
clause lets you specify the entire row of an object table.
Restriction on the VALUE clause You can specify this clause only for an object table.
Note:
If you insert string literals into aRAW
column, then during subsequent queries, Oracle Database will perform a full table scan rather than using any index that might exist on the RAW
column.where_clause
The where_clause
lets you restrict the rows updated to those for which the specified condition
is true. If you omit this clause, then the database updates all rows in the table or view. Refer to Chapter 7, 'Conditions' for the syntax of condition
.
Pokemon silver rom download gba. Download Pokemon - Silver Version ROM for Gameboy Color(GBC) and Play Pokemon - Silver Version Video Game on your PC, Mac, Android or iOS device!
The where_clause
determines the rows in which values are updated. If you do not specify the where_clause
, then all rows are updated. For each row that satisfies the where_clause
, the columns to the left of the equality operator (=) in the update_set_clause
are set to the values of the corresponding expressions to the right of the operator. The expressions are evaluated as the row is updated.
returning_clause
The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.
When operating on a single row, a DML statement with a returning_clause
can retrieve column expressions using the affected row, rowid, and REFs
to the affected row and store them in host variables or PL/SQL variables.
When operating on multiple rows, a DML statement with the returning_clause
stores values from expressions, rowids, and REFs
involving the affected rows in bind arrays.
exprEach item in the expr
list must be a valid expression syntax.
INTO The INTO
clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item
list.
data_itemEach data_item
is a host variable or PL/SQL variable that stores the retrieved expr
value.
For each expression in the RETURNING
list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO
list.
Restrictions The following restrictions apply to the RETURNING
clause:
The
expr
is restricted as follows:For
UPDATE
andDELETE
statements eachexpr
must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the samereturning_clause
. ForINSERT
statements, eachexpr
must be a simple expression. Aggregate functions are not supported in anINSERT
statementRETURNING
clause.Single-set aggregate function expressions cannot include the
DISTINCT
keyword.
If the
expr
list contains a primary key column or otherNOT
NULL
column, then the update statement fails if the table has aBEFORE
UPDATE
trigger defined on it.You cannot specify the
returning_clause
for a multitable insert.You cannot use this clause with parallel DML or with remote objects.
You cannot retrieve
LONG
types with this clause.You cannot specify this clause for a view on which an
INSTEAD
OF
trigger has been defined.
See Also:
Oracle Database PL/SQL Language Reference for information on using theBULK
COLLECT
clause to return multiple values to collection variableserror_logging_clause
The error_logging_clause has the same behavior in an UPDATE
statement as it does in an INSERT
statement. Refer to the INSERT
statement error_logging_clause for more information.
See Also:
'Inserting Into a Table with Error Logging: Example'Examples
Updating a Table: Examples The following statement gives null commissions to all employees with the job SH_CLERK
:
The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:
The following statement increases the salary of an employee in the employees
table on the remote
database:
The next example shows the following syntactic constructs of the UPDATE
statement:
Both forms of the
update_set_clause
together in a single statementA correlated subquery
A
where_clause
to limit the updated rows
The preceding UPDATE
statement performs the following operations:
Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
Sets
department_id
for these employees to thedepartment_id
corresponding to Bombay (location_id
2100)Sets each employee's salary to 1.1 times the average salary of their department
Sets each employee's commission to 1.5 times the average commission of their department
Updating a Partition: Example The following example updates values in a single partition of the sales
table:
Updating an Object Table: Example The following statement creates two object tables, people_demo1
and people_demo2
, of the people_typ
object created in Table Collections: Examples. The example shows how to update a row of people_demo1
by selecting a row from people_demo2
:
The example uses the VALUE
object reference function in both the SET
clause and the subquery.
Correlated Update: Example For an example that uses a correlated subquery to update nested table rows, refer to 'Table Collections: Examples'.
Using the RETURNING Clause During UPDATE: Example The following example returns values from the updated row and stores the result in PL/SQL variables bnd1
, bnd2
, bnd3
:
The following example shows that you can specify a single-set aggregate function in the expression of the returning clause:
The UPDATE
statement changes the values of specified columns in one or more rows in a table or view. For a full description of the UPDATE
SQL statement, see Oracle Database SQL Reference.
Syntax
update statement ::=
Description of the illustration update_statement.gif
Keyword and Parameter Description
alias
Another (usually short) name for the referenced table or view, typically used in the WHERE
clause.
column_name
The column (or one of the columns) to be updated. It must be the name of a column in the referenced table or view. A column name cannot be repeated in the column_name
list. Column names need not appear in the UPDATE
statement in the same order that they appear in the table or view.
returning_clause
Returns values from updated rows, eliminating the need to SELECT
the rows afterward. You can retrieve the column values into variables or host variables, or into collections or host arrays. You cannot use the RETURNING
clause for remote or parallel updates. If the statement does not affect any rows, the values of the variables specified in the RETURNING
clause are undefined. For the syntax of returning_clause
, see 'RETURNING INTO Clause'.
SET column_name = sql_expression
This clause assigns the value of sql_expression
to the column identified by column_name
. If sql_expression
contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign.
SET column_name = (subquery3)
Assigns the value retrieved from the database by subquery3
to the column identified by column_name
. The subquery must return exactly one row and one column.
SET (column_name, column_name, ..) = (subquery4)
Assigns the values retrieved from the database by subquery4
to the columns in the column_name
list. The subquery must return exactly one row that includes all the columns listed. The column values returned by the subquery are assigned to the columns in the column list in order. The first value is assigned to the first column in the list, the second value is assigned to the second column in the list, and so on.
sql_expression
Any valid SQL expression. For more information, see Oracle Database SQL Reference.
subquery
A SELECT
statement that provides a set of rows for processing. Its syntax is like that of select_into_statement
without the INTO
clause. See 'SELECT INTO Statement'.
table_reference
A table or view that must be accessible when you execute the UPDATE
statement, and for which you must have UPDATE
privileges. For the syntax of table_reference
, see 'DELETE Statement'.
TABLE (subquery2)
The operand of TABLE
is a SELECT
statement that returns a single column value, which must be a nested table or a varray. Operator TABLE
informs Oracle that the value is a collection, not a scalar value.
WHERE CURRENT OF cursor_name
Refers to the latest row processed by the FETCH
statement associated with the specified cursor. The cursor must be FOR
UPDATE
and must be open and positioned on a row. If the cursor is not open, the CURRENT
OF
clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND
.
WHERE search_condition
Chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit this clause, all rows in the table are updated.
Usage Notes
You can use the UPDATE
WHERE
CURRENT
OF
statement after a fetch from an open cursor (including fetches done by a cursor FOR
loop), provided the associated query is FOR
UPDATE
. This statement updates the row that was just fetched.
The implicit cursor SQL
and the cursor attributes %NOTFOUND
, %FOUND
, %ROWCOUNT
, and %ISOPEN
let you access useful information about the execution of an UPDATE
statement.
Oracle Update Statement Using Subquery
Examples
Example 13-7 creates a table with correct employee IDs but garbled names. Then it runs an UPDATE
statement with a correlated query, to retrieve the correct names from the EMPLOYEES
table and fix the names in the new table.
For examples, see the following:
Example 1-12, 'Creating a Stored Subprogram'
Example 4-1, 'Using a Simple IF-THEN Statement'
Example 5-51, 'Updating a Row Using a Record'
Example 5-52, 'Using the RETURNING Clause with a Record'
Example 6-1, 'Data Manipulation With PL/SQL'
Oracle Update Statement Example
Example 6-5, 'Using CURRVAL and NEXTVAL'
Example 6-6, 'Using ROWNUM'
Example 6-38, 'Using SAVEPOINT With ROLLBACK'
Example 6-41, 'Using CURRENT OF to Update the Latest Row Fetched From a Cursor'
Example 7-1, 'Examples of Dynamic SQL'
Example 7-5, 'Dynamic SQL with RETURNING BULK COLLECT INTO Clause'
Example 7-6, 'Dynamic SQL Inside FORALL Statement'
Example 11-6, 'Using Rollbacks With FORALL'
Example 11-9, 'Bulk Operation That Continues Despite Exceptions'
Related Topics
'Data Manipulation'
'DELETE Statement'
'FETCH Statement'
'INSERT Statement'