Database Tutorial

Week #6: Articles

Learning Outcomes

• Learn how to update tables with values.
• Learn how to update tables with subqueries.
• Learn how to update tables with correlated subqueries.
• Learn how to update tables with the RETURNING INTO clause.

UPDATE Statement

The UPDATE statement lets you change data in tables and views by resetting values in one or more columns. A single UPDATE statement can change one, many, or all rows in a table. The new values can come from literal values, variables, or correlated query results. Correlation is the matching of one set of data with another through join operations. This section discusses equijoin (equality value joins); later in this appendix we’ll examine join options in detail in the “Join Results” section.

Oracle implements the basic UPDATE statement syntax in an ANSI-compliant way, but Oracle supports both a record update and a large object update (through the dbms_lob package). The large object update appends the RETURNING INTO clause to UPDATE statements.

This section covers how you do the following:

• Update by values and queries
• Update by correlated queries

An UPDATE statement’s most important behavior is that it works against all rows in a table unless a WHERE clause or a correlated join limits the number of rows. This means you should always limit which rows should be changed by providing a WHERE clause or a correlated join. The list of columns in the SET clause of an UPDATE statement is the expression list.

Changes by the UPDATE statement are hidden until committed in an Oracle database, but are immediately visible in nontransactional databases. Oracle is always in transaction mode by default.

Generic Update by Values and Queries

Some UPDATE statements use date, numeric, or string literals in the SET subclause. The SET subclause can work with one to all columns in a table, but you should never update a primary surrogate key column. Any update of the externally known identifier column risks compromising the referential integrity of primary and foreign keys.

The generic UPDATE statement prototype with values resetting column values looks like this:

UPDATE      some_table
SET         column_name = 'expression'
[,          column_name = 'expression' [, ...]
WHERE [NOT] column_name {{= | <> | > | >= | < | <=} |
[NOT] {{IN | EXISTS} | IS NULL}} 'expression'
[{AND | OR } [NOT] comparison_operation] [...];

The target table of an UPDATE statement can be a table or updateable view. An expression can be a numeric or string literal or the return value from a function or subquery. The function or subquery must return only a single-row of data that matches the data type of the assignment target. The right operand of the assignment may contain a different data type when its type can be implicitly cast to the column’s data type, or explicitly cast to it with the CAST function. In the generic example, a subquery needs to return a single column and row (this type of subquery is
a scalar subquery or SQL expression). Ellipses replace multiple listings in the SET and WHERE clauses.

The WHERE clause lets you evaluate truth or non-truth, which is the purpose of each comparison operation. The comparison operators in the prototype are broken into sets of related operators by using curly braces: first the math comparisons, then the set and correlation comparisons, and finally the null comparison. The AND, OR, and NOT are logical operators. The AND operator evaluates the truth of two comparisons or, with enclosing parentheses, the truth of sets of comparison operations. The OR operator evaluates the truth of one or the other comparison operator, and it employs short-circuit evaluation (the statement is true when the first comparison is true). The negation operator (NOT) checks whether a statement is false.

An actual UPDATE statement against an item table would look like this when you enter the actual movie name in lieu of a placeholder value:

SQL> UPDATE item
  2  SET    item_title = 'Pirates of the Caribbean: On Stranger Tides'
  3  ,      item_rating = 'PG-13'
  4  WHERE  item_title = 'Pirates of the Caribbean 4';

Variations to this syntax exist in Oracle, but this is the basic form for UPDATE statements. Specifics for how Oracle handles it are provided in the following section.

Oracle Update by Values and Queries

The biggest difference between Oracle and other databases is that Oracle allows you to reset record structures, not just columns. Recall from the discussion of tables earlier in this appendix that the definition of a table is equivalent to the definition of a record structure, and a record structure is a combination of two or more columns (or fields).

The prototype of an UPDATE statement for Oracle differs from the generic profile, as you can see:

UPDATE {some_table | TABLE(query_statement)}
SET    {column_name = 'expression' | (column_list) = 'expression_list'}
[,     {column_name = 'expression' | (column_list) = 'expression_list'}
[, ...]]
WHERE [NOT] {column_name | (column_list)}
              {{= | <> | > | >= | < | <=} |
                [NOT] {IN | =ANY | =SOME | =ALL } |
                [NOT] {IS NULL | IS SET} | [NOT] EXISTS} 'expression'
[{AND | OR } [NOT] comparison_operation] [...]
[RETURNING {column_name | (column_list)} INTO {local_variable | (variable_list)}];

Oracle extends the target of the UPDATE statement from a table or view (traditionally a named query inside the data catalog) to a result set. In Oracle’s lexicon, the result set is formally an aggregate result set, which is a fancy way of saying that the result set acts like a normal query’s return set in processing memory (inside the System Global Area, or SGA). The TABLE function makes this possible. (The TABLE function was previously known as the THE function—that’s ancient history from Oracle 8i, although some error message have never been updated and still reflect this relic.)

Oracle also extends the behavior of assignment in the SET operator by allowing you to assign a record structure to another record structure. A (data) record structure in the SET operator is any list of two or more columns from the table definition, which is less than the complete data structure of the table or its definition in the data catalog. Ellipses replace continuing the list of possible elements in the SET and WHERE clauses.

The WHERE clause comparison operators are also expanded in an Oracle database. They’re separated by curly braces, like the generic prototype, with math comparisons, set comparisons, null comparisons, and correlation. Set comparisons act as lookup operators, and correlation is explained in the “Update by Correlated Queries” section of this article.

The RETURNING INTO clause allows you to shift a reference to columns that you’ve updated but not committed into variables. Those variables are critical to how you update large objects in the database.

Here’s an example of how you would use Oracle’s record structure assignment operation in a SET clause:

SQL> UPDATE item
  2  SET   (item_title, item_rating) =
  3          (SELECT 'Pirates of the Caribbean: On Stranger Tides'
  4           ,      'PG-13'
  5           FROM   dual)
  6  WHERE  item_title = 'Pirates of the Caribbean 4';

The values reset the columns item_title and item_rating on all lines where item_ title is “Pirates of the Caribbean 4.” The subquery uses string literals inside a query against the dual table. This is straightforward and not much different from the comma-delimited SET clauses for each column. You might wonder why you should bother with implementing this twist on the other syntax. That’s a great question! There’s not much added value with date, numeric, or string literals from the pseudo-table dual; rather, the value occurs when the source is a row returned from a query. The record structure syntax allows you to assign a row’s return values directly from a single-row subquery with multiple columns to a row of the target table.

Here’s an example of an assignment from a subquery to record structure:

SQL> UPDATE item
  2  SET (i.item_title, i.item_rating) =
  3          (SELECT ii.item_title, ii.item_rating
  4           FROM   import_item ii
  5           WHERE  ii.item_barcode = 'B004A8ZWUG')
  6  WHERE  i.item_title = 'Pirates of the Caribbean 4';

The item_title and item_rating values from the subquery are assigned to the equivalent columns in the item table when the item_title column holds the string literal value. The power of this type of assignment increases when you add correlation, because you can process sets of data in a single UPDATE statement. (That’s covered in the “Update by Correlated Queries” section later in the article.)

Two specialized forms of UPDATE statements are included in the Oracle database. One works with collections of object types, and the other works with scalar and large object types. The ability to use the result of the TABLE function inside an UPDATE statement lets you update nested tables (collections of object types). A RETURNING INTO clause supports scalar and large objects by returning the values or references from the UPDATE statement to the calling scope. The calling scope is the SQL*Plus session in the examples but could be an external program written in PL/SQL or C, C++, C#, or Java. This technique provides you with access to recently updated values without requerying the table, and in the case of large objects this technique allows you to read and write to large objects through a web application.

RETURNING INTO Clause

You can append the RETURNING INTO clause to any UPDATE statement. The RETURNING INTO clause lets you retrieve updated column values into locally scoped variables. This lets you avoid requerying the columns after the UPDATE statement.

We can use a brief example to demonstrate this concept because even the shortest example can use session-level bind variables. The bind variables eliminate the need for a procedural programming language such as Java or PHP to demonstrate the concept. This example requires a pair of session-level variables to act as the target of the RETURNING INTO clause. You can declare these two bind variables with this syntax:

SQL> VARIABLE bv_title  VARCHAR2(60)
SQL> VARIABLE bv_rating VARCHAR2(60)

The following demonstrates an UPDATE statement that uses the RETURNING INTO phrase: SQL> UPDATE item

SQL> UPDATE   item
  2  SET     (item_title, item_rating) = 
  3  ,          ((SELECT 'Pirates of the Caribbean: On Stranger Tides','PG-13')
  4               FROM     dual)
  5  WHERE    item_title = 'Pirates of the Caribbean 4')
  6  RETURNING item_title, item_rating INTO :bv_title, :bv_rating;

The values updated into the table are returned in the local variables. They can be displayed by using SQL*Plus formatting and a query:

COLUMN bv_title  FORMAT A44 HEADING ":bv_title"
COLUMN bv_rating FORMAT A12 HEADING ":bv_rating"
SELECT :bv_title AS bv_title, :bv_rating AS bv_rating FROM dual;

The HEADING value is enclosed in double quotes so that a colon can be used in the column titles. This returns the literal values from the query against the dual table:

:bv_title                                    :bv_rating
-------------------------------------------- ------------
Pirates of the Caribbean: On Stranger Tides  PG-13

Note that the RETURNING INTO phrase has several restrictions:

• It fails when updating more than a single-row.

Returning scalar, BLOB, or CLOB data types is the most effective way to leverage the RETURNING INTO phrase. The RETURNING INTO phrase is very advantageous in web applications. A web application would implement a stored procedure to start a transaction context and pass a reference for updating a CLOB column.

SQL> CREATE OR REPLACE PROCEDURE web_load_clob_from_file
  2  ( pv_item_id IN NUMBER
  3  , pv_descriptor IN OUT CLOB ) IS
  4 BEGIN
  5    -- A FOR UPDATE makes this a DML transaction.
  6    UPDATE item
  7    SET    item_desc = EMPTY_CLOB()
  8    WHERE  item_id = pv_item_id
  9    RETURNING item_desc INTO pv_descriptor;
 10  END web_load_clob_from_file;
 11  /

The pv_descriptor parameter in the procedure’s signature on line 3 uses an IN OUT mode of operation, which is a pass-by-reference mechanism. It effectively enables sending a reference to the CLOB column out to the calling program. The RETURNING INTO clause assigns the reference to the parameter on line 9. With the reference, the external program can then update the CLOB column.

Update by Correlated Queries

A correlated query is a specialized subquery that contains a join to an outer query. An UPDATE statement can contain a correlated in the SET clause, which allows the UPDATE statement to change multiple rows with potentially different values.

SQL> UPDATE   rental_item ri 
  2  SET      rental_item_type =
  3            (SELECT   cl.common_lookup_id
  4             FROM     common_lookup cl
  5             WHERE    cl.common_lookup_code =
  6                       (SELECT TO_CHAR(r.return_date - r.check_out_date)
  7                        FROM rental r
  8                        WHERE r.rental_id = ri.rental_id));

Line 1 designates the rental_item table as the target of the UPDATE statement, and line 1 assigns a ri table alias to the rental_item table. Line 2 assigns the result of a correlated subquery to the rental_item_type column. Line 8 matches the rows to update in the rental_item table with the rental table based on matching the primary key value to the foreign key value. The rental_id column is the surrogate and primary key column of the rental table, while the rental_id column in the rental_item table is a foreign key column.