SAND CDBMS SQL Reference Guide
UPDATE

 

The UPDATE command modifies the values of specified columns in zero or more rows of a table. These updated rows can optionally be selected with a search condition. There are additional options to update one table based on the contents of another, and to update a BLOB column with the contents of a specified file.


Required Privileges

In order to update a table, the user authorization must own the table, own or possess OWNER privileges on the schema to which the table belongs, possess UPDATE privileges on the table, or possess DBA privileges. If the FROM clause is used, the user must own each source table, own or possess OWNER privileges on the schema(s) to which the source tables belong, possess SELECT privileges on the source tables, or possess DBA privileges.


Syntax



table name
The table name argument identifies an existing table. If the table is not in the current schema, prefix the table name with the name of the appropriate schema followed by a period ( . ), that is, schema-name.table-name.

column name
Introduced by the keyword SET, the column name argument names a table column that will take on the value of the assigned value expression, or else the null value. One or more columns with their assigned values can be specified (with each assignment expression separated by a comma).

Note: A BLOB column can be updated with data from a file using a specialized syntax. Refer to the "Updating a BLOB Column with the Contents of a File" section below for more information.

value expression
Specifies an expression made up of constants, column names, scalar subqueries, functions (non-aggregate only), and/or arithmetic operators (+, -, *, /) that will return the new value of column name. Alternatively, the keyword NULL may be specified to set the column to null.

Boolean value expression
Preceded by the keyword WHERE, the Boolean value expression argument specifies a search condition for the optional WHERE clause. When combined with a FROM clause, the WHERE clause can be used to limit the update values to one per target row, since an error condition is returned if the UPDATE statement attempts to update the same row more than once.


Table Expression Clause


table name
view name
These identify a table or view involved in the update. At least one table name in the FROM clause must reference the table being updated. The table/view can also be a source of data, or an operand in the WHERE clause.

( <SELECT statement> )
A subquery (nested table expression), which is a complete SELECT statement, can appear in the FROM clause. The output of the subquery constitutes a "virtual" table that can be referenced in other parts of the UPDATE statement, as if it were a base table or view. The subquery must be contained in parentheses, and it must be given a correlation name so that it can be referenced elsewhere. Refer to Nested Table Expressions for further information and examples.

correlation name
This is an alias for the table, view, or subquery, used to identify the database object in qualified column references within the UPDATE statement. A correlation name is optional for a table or view, since the actual table/view name can be used to qualify column references. A correlation name is required for a subquery, which cannot be referenced in any other way.

Note:
If the same table name or view name appears more than once in the FROM clause, only one of these table/view references can appear without a correlation name.


Joined Table Clause


qualified join

A join may be performed on two or more tables or subqueries. Join types include INNER JOIN (the equivalent of simply specifying JOIN), OUTER JOIN (LEFT, RIGHT, or FULL), and MATCH JOIN (LEFT or RIGHT). Consult the section on Joins for an explanation of the different types of joins.

( joined table )
A table expression (table name or qualified join specification) can be included in parentheses to specify that it should be evaluated before other table expressions in the UPDATE statement. For further information about nested joins, consult the Nested Joins section.


Qualified Join Clause


Note: Consult the Joins section for an explanation of the different types of joins.


Joined Table Expression Clause


( joined table expression )

A joined table expression can optionally be enclosed by parentheses. The use of parentheses does not affect the order of processing in this case, since only the AND operator can be used to combine join predicates.

column name
The first column name argument is a reference to a column from one of the joined tables; the column name argument following the comparison operator must refer to a column from the other joined table. Comparisons to constants cannot appear in a join predicate.

AND
Multiple join predicates can be combined using the AND Boolean operator only. That is, OR conditions are not permitted in the joined table expression clause.


Description

The UPDATE command is used to change the values stored in a table. The SET clause specifies which fields in the target table are affected and how they are to be updated, while the WHERE clause limits the update to those records that satisfy the specified conditions (providing all integrity conditions are met and re-verified). The WHERE search condition may be an expression or a nested query. If the WHERE clause is omitted, all records in the target table will be updated.

When the FROM clause is included in the UPDATE statement, the target table can be updated with data from one or more other tables or views. The UPDATE FROM clause is identical to the SELECT FROM clause, so all types of table references, including joined table expressions and nested table expressions, are supported. The table that is the target of the UPDATE must also appear in the FROM clause, or an error message will be returned. If there is more than one reference in the FROM clause that matches the target table, the first match is used. The column(s) being updated cannot be qualified by table or table alias, as qualification is unnecessary in this situation.


Updating a BLOB Column with the Contents of a File

There is a special type of UPDATE...FROM command that can be used to update a BLOB column with data from an external file. While a standard UPDATE statement can update a BLOB column with character data, the UPDATE...FROM 'file' command can update a BLOB column with data containing unprintable characters. In addition, the UPDATE...FROM 'file' command can read up to 8192 KB (8 MB) from the source file, which represents a considerably larger update value than can be specified in practice through a standard UPDATE statement.

The syntax for the UPDATE...FROM 'file' command is the following:


table name
I
dentifies an existing table that contains a BLOB column. If the table is not in the current schema, prefix the table name with the name of the appropriate schema followed by a period ( . ), that is, schema-name.table-name.

column name
Identifies the BLOB column that will be updated.

'data file'
Specifies the data file whose contents will be used to update the BLOB column. The file name by itself can be specified if the file resides in the same directory as the primary database file; otherwise, the full or relative path of the file must be provided. The whole parameter must be contained in single quotation marks. Note that if the target file is larger than 8192 KB (8 MB), the BLOB column will be updated with only the first 8 MB of data with no error or warning message.

Boolean value expression
The Boolean value expression that will be tested against each record in the target table. If the expression evaluates to TRUE for a record, the BLOB column (column name) will be updated with the specified data for that record. If it evaluates to FALSE, the record is left unchanged.


Examples

The following are examples of UPDATE commands. The first example changes the value of the COLOR column for part number P2 to BROWN, and then adds 5 to its WEIGHT column value.

   UPDATE part
     SET color = 'BROWN', weight = weight + 5
     WHERE pno = 'P2';

The next command doubles the STATUS column value of all London suppliers:

   UPDATE supplier
     SET status = status * 2
     WHERE city = 'LONDON';

The WHERE clause may also include a nested query, as in the following example, which updates the records in the PARTSUPP table that contain the part numbers of any part made in Paris:

   UPDATE partsupp
     SET qty = qty-100
     WHERE pno IN
       (SELECT pno FROM part
          WHERE city = 'PARIS');

While an UPDATE command can update only one table, its WHERE clause may consist of a nested query that specifies a join between several tables. For example:

UPDATE supplier
  SET status = 30
  WHERE sno IN
    (SELECT sno FROM partsupp, part
       WHERE partsupp.pno = part.pno
       AND qty > 50);

Including a FROM clause allows the table to be updated with values from another table. In this example, the price of items in the PRODUCTS tables is updated using a price adjustment from the P_ADJUST table. Note that the table being updated must be included in the FROM list.

   UPDATE products
     SET price = p.price + a.price_adj
     FROM products p, p_adjust a
     WHERE p.p_no = a.p_no;


The following SQL statement updates the BLOB column blob_col in table work with data from an external file named "adminXML.log":

UPDATE work
  SET blob_col
  FROM 'C:\work\tmp\adminXML.log'
  WHERE date_col < '2007-01-01' ;

In this example, the WHERE clause limits the update to only those records whose date_col value is earlier than January 1, 2007.