Multiple-Column Format

Use the multiple-column format of the SET clause to list multiple columns and set them equal to corresponding expressions. 
(1)
Multiple-Column Format

1  ( + , column )
1 *
2   = 
2  (
3.1+ ,
3.1 expression
3.2.1 + , ( singleton_select )
3.2.1 NULL
2 )
Notes:
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
expression Expression that returns a value for a column Cannot include aggregate functions Expression
singleton_ select Subquery that returns exactly one row Values that the subquery returns must correspond to columns in the column list SELECT statement
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
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';