UPDATE keyword

Updates data in a database table.

Syntax#

Flow chart showing the syntax of the UPDATE keyword

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.

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 join

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

Update with multiple joins

UPDATE spreads s
SET s.spread = p.ask - p.bid
FROM prices p
JOIN instruments i ON p.symbol = i.symbol
WHERE s.timestamp = p.timestamp AND i.type = 'BOND';

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;