You can execute the UPDATE statement to update the values of specified columns in all rows that meet the specified condition in a table. This topic describes how to use the UPDATE statement in Hologres.

Limits

  • The UPDATE statement does not update distribution keys.
  • The UPDATE statement updates child partitioned tables but not parent partitioned tables.
  • We recommend that you use the INSERT ON CONFLICT statement instead of the UPDATE statement. The UPDATE statement uses table-level locks and requires high execution overheads and a long lock period. The INSERT ON CONFLICT statement uses row-level locks and provides high efficiency at low costs. For more information, see INSERT ON CONFLICT.
  • We recommend that you use fixed plans to optimize execution efficiency. For more information, see UPDATE statements.

Syntax

The UPDATE statement uses the following syntax: 

UPDATE table [ * ] [ [ AS ] alias ]
    SET { column = { expression } 
          ( column [, ...] ) = ( { expression } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition ]
The following table describes the parameters in the syntax.
Parameter Description
alias The substitute name for the table to update.
expression The expression to assign an updated value to a column.
condition The condition that rows to be updated must meet.

Example

The following example show how to use the UPDATE statement in Hologres: 

CREATE TABLE update_test (
  a text primary key, 
  b int not null, 
  c text not null, 
  d text);  

INSERT INTO update_test VALUES ('b1', 10, '', '');

UPDATE update_test SET b = b + 10 where a = 'b1';
UPDATE update_test SET c = 'new_'  a, d = null where b = 20;
UPDATE update_test SET (b,c,d) = (1, 'test_c', 'test_d'); 

CREATE TABLE tmp(a int);
INSERT INTO tmp VALUES (2);
UPDATE update_test SET b = tmp.a FROM tmp;

For more information about the UPDATE statement, see UPDATE.