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

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 Common Table Expression

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;