UPDATE¶

The UPDATE statement page describes the following:

Overview ¶

The UPDATE command is used to modify the value of certain columns in existing rows without creating a table.

It can be used to do the following:

Performing localized changes in existing data, such as correcting mistakes discovered after ingesting data.
Setting columns based on the values of others.

Warning

Using the UPDATE command on column clustered using a cluster key can undo your clustering.

The following is the correct syntax for the UPDATE command:

UPDATE target_table_name [[AS] alias1]
SET column_name = expression [,...]
[WHERE condition]

The following is the correct syntax for triggering a clean-up:

SELECT cleanup_chunks('schema_name','table_name');
SELECT cleanup_extents('schema_name','table_name');
SELECT cleanup_discarded_chunks(‘public’,’t’);

The following table describes the UPDATE parameters:

Parameter	Description
`target_table_name`	Specifies the table containing the data to be updated.
`column_name`	Specifies the column containing the data to be updated.
`additional_table_name`	Additional tables used in the WHERE condition for performing complex joins.
`condition`	Specifies the condition for updating the data.

Note

Similar to a DELETE statement, an UPDATE statement may leave some uncleaned data behind, which requires a clean-up operation.

The Examples section includes the following examples:

The following is an example of performing a simple update:

UPDATE bands SET records_sold = records_sold + 1 WHERE name LIKE 'The %';

The following section shows an example of triggering a clean-up:

SELECT * FROM sqream_catalog.discarded_chunks;
SELECT cleanup_discarded_chunks('public','t');

The following is an example of the output generated from the above:

Executing the UPDATE statement obtains an exclusive UPDATE lock on the target table.

Executing an UPDATE statement requires both UPDATE and SELECT permissions on the target table.