Multiple-Column Format

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > UPDATE >

Multiple-Column Format

Use the multiple-column format of the SET clause to list multiple columns and set them equal to corresponding expressions.

Multiple-Column Format:
 
        .-,------.
        V        |
|--+-(----column-+--)-+-- =------------------------------------->
   '-*----------------'
 
      .-,--------------------------------------------------------.
      V                                                          |
>--(----+-expression-------------------------------------------+-+--)--|
        |   .-,----------------------.                         |
        |   V                        |                         |
        +-+---(--singleton_select--)-+-+-----------------------+
        | '-NULL-----------------------'                       |
        |  (1)                                                 |
        '--------SPL function--(--+-----------------------+--)-'
                                  | .-,-----------------. |
                                  | V              (2)  | |
                                  '---| Argument |------+-'

Notes:

Extended Parallel Server only
See page Arguments

Element	Description	Restrictions	Syntax
column	Name of a column to be updated	Cannot have a serial or ROW type. The number of column names must equal the number of values returned to the right of the = sign.	Identifier, p. Identifier
expression	Expression that returns a value for a column	Cannot include aggregate functions	Expression, p. Expression
singleton_ select	Subquery that returns exactly one row	Values that the subquery returns must correspond to columns in the column list	SELECT, p. SELECT
SPL function	SPL routine that returns one or more values	Returned values must have a 1-to-1 correspondence to columns in the column list	Identifier, p. Identifier

The multiple-column format of the SET clause offers the following options for listing a set of columns that you intend to update:

Explicitly list each column, placing commas between columns and enclosing the set of columns between parentheses.
Implicitly list all columns in the table by using an asterisk ( * ).

You must list each expression explicitly, placing comma ( , ) separators between expressions and enclosing the set of expressions between parentheses. The number of columns must equal the number of values returned by the expression list, unless the expression list includes an SQL subquery.

The following examples show the multiple-column format of the SET clause:

UPDATE customer
   SET (fname, lname) = ('John', 'Doe') WHERE customer_num = 101

UPDATE manufact
   SET * = ('HNT', 'Hunter') WHERE manu_code = 'ANZ'