Home  /  Reference

UPDATE

UPDATE changes values stored in user-created tables.

Syntax

UPDATE table_name AS alias SET column_name = expr , WHERE condition
Field Use
UPDATE table_name The table whose values you want to update.
alias Only permit references to table_name as alias.
SET col_ref = expr Assign the value of expr to col_ref.
WHERE condition Only update rows which evaluate to true for condition.

Details

Known limitations

  • UPDATE cannot be used inside transactions.
  • UPDATE can reference user-created tables but not sources.
  • Low performance. While processing an UPDATE statement, Materialize cannot process other INSERT, UPDATE, or DELETE statements.

Examples

CREATE TABLE update_me (a int, b text);
INSERT INTO update_me VALUES (1, 'hello'), (2, 'goodbye');
UPDATE update_me SET a = a + 2 WHERE b = 'hello';
SELECT * FROM update_me;

 a |    b
---+---------
 3 | hello
 2 | goodbye

UPDATE update_me SET b = 'aloha';
SELECT * FROM update_me;

 a |   b
---+-------
 2 | aloha
 3 | aloha

Privileges

The privileges required to execute this statement are:

  • USAGE privileges on the schemas that all relations and types in the query are contained in.
  • UPDATE privileges on table_name.
  • SELECT privileges on all relations in the query.
    • NOTE: if any item is a view, then the view owner must also have the necessary privileges to execute the view definition. Even if the view owner is a superuser, they still must explicitly be granted the necessary privileges.
  • USAGE privileges on all types used in the query.
  • USAGE privileges on the active cluster.
Back to top ↑