UPDATE keyword

Updates data in a database table.

Syntax

UPDATE tableName [alias]
SET columnName = expression [, columnName = expression ...]
    [FROM joinTable [JOIN joinTable2 ON joinCondition] ...]
    [WHERE filter];

note

the same columnName cannot be specified multiple times after the SET keyword as it would be ambiguous
the designated timestamp column cannot be updated as it would lead to altering history of the time-series data
If the target partition is attached by a symbolic link, the partition is read-only. UPDATE operation on a read-only partition will fail and generate an error.
On a WAL table, UPDATE is applied by the WAL apply job and counts against cairo.wal.apply.memory.limit.bytes rather than the query memory limit. This holds for every form of UPDATE a WAL table accepts, including one with a subquery in its SET or WHERE clause: the whole statement is written to the WAL and executed by the apply job. UPDATE ... FROM, which joins another table, is not supported on WAL tables and is rejected with UPDATE statements with join are not supported yet for WAL tables. On a non-WAL table, UPDATE runs on the caller's connection under the caller's query memory limit.

Examples

Update with constant

UPDATE trades SET price = 125.34 WHERE symbol = 'AAPL';

Update with function

UPDATE book SET mid = (bid + ask)/2 WHERE symbol = 'AAPL';

Update with subquery

UPDATE spreads s SET spread = p.ask - p.bid FROM prices p WHERE s.symbol = p.symbol;

Update with multiple joins
WITH up AS (
    SELECT p.ask - p.bid AS spread, p.timestamp
    FROM prices p
    JOIN instruments i ON p.symbol = i.symbol
    WHERE i.type = 'BOND'
)
UPDATE spreads s
SET spread = up.spread
FROM up
WHERE s.timestamp = up.timestamp;

Update with a sub-query
WITH up AS (
    SELECT symbol, spread, ts
    FROM temp_spreads
    WHERE timestamp between '2022-01-02' and '2022-01-03'
)
UPDATE spreads s
SET spread = up.spread
FROM up
WHERE up.ts = s.ts AND s.symbol = up.symbol;

Syntax​

Examples​

Syntax

Examples