• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. UPDATE

UPDATE

Function

UPDATE changes the values of the specified columns in all rows that satisfy the condition. The WHERE clause clarifies conditions. The columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

Precautions

  • You must have the UPDATE permission on a table to be updated.
  • You must have the SELECT permission on all tables involved in the expressions or conditions.
  • The distribution column of a table cannot be modified.
  • For column-store tables, the RETURNING clause is currently not supported.
  • Column-store tables do not support non-deterministic update. If you update data in one row with multiple rows of data in a column-store table, an error is reported.
  • Memory space recording update operations in column-store tables is not reclaimed. You need to clean it executing VACUUM FULL table_name.

Syntax

UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET {column_name = { expression | DEFAULT } 
    |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] )                                |sub_query }}[, ...]
    [ FROM from_list] [ WHERE condition ]
    [ RETURNING {* 
                | {output_expression [ [ AS ] output_name ]} [, ...] }];

Parameter Description

  • table_name

    Name (optionally schema-qualified) of the table to be updated.

    Value range: an existing table name

  • alias

    Specifies the alias for the target table.

    Value range: a string compliant with the naming convention.

  • column_name

    Renames a column.

    You can refer to this column by specifying the target table alias and the column name. For example:

    UPDATE foo AS f SET f.col_name = 'postgres';

    Value range: an existing column name

  • expression

    An expression or value to assign to the column.

  • DEFAULT

    Sets the column to its default value.

    The value is NULL if no specified default value has been assigned to it.

  • sub_query

    Subqueries

    This command can be executed to update a table with information for other tables in the same database.

  • from_list

    A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM clause of a SELECT statement.

    Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

  • condition

    An expression that returns a value of type boolean. Only rows for which this expression returns true are updated.

  • output_expression

    An expression to be computed and returned by the UPDATE command after each row is updated.

    Value range: The expression can use any column names of the table named by table_name or table(s) listed in FROM. Write * to return all columns.

  • output_name

    A name to use for a returned column.

Example

-- Create the student1 table:
CREATE TABLE student1
(
   stuno     int,
   classno   int 
)
DISTRIBUTE BY hash(stuno);

-- Insert data.
INSERT INTO student1 VALUES(1,1);
INSERT INTO student1 VALUES(2,2);
INSERT INTO student1 VALUES(3,3);

-- View data.
SELECT * FROM student1;

-- Update the values of all records:
UPDATE student1 SET classno = classno*2;

-- View data.
SELECT * FROM student1;

-- Delete the table.
DROP TABLE student1;