UPDATE

Changes column values in the rows that match a condition. The primary key cannot be changed.

Syntax

UPDATE <table> SET <column> = <expression> [, <column> = <expression>] ... [WHERE <condition>]
UPDATE <table> SET (<column>, ...) = (<expression>, ...) [WHERE <condition>]

WHERE takes the same conditions as SELECT, subqueries included. Without WHERE, every row is updated.

Examples

Set a value

UPDATE tickets SET baggage_kg = 10 WHERE id = 4;

UPDATED 1

Compute from the current value

UPDATE tickets SET baggage_kg = baggage_kg + 5 WHERE id = 5;

This is one statement, with no gap between reading and writing in which another writer’s change could be lost.

Swap two columns

Every expression reads the row as it was before the statement, so this swaps:

UPDATE flights SET origin = dest, dest = origin WHERE id = 2;

Use a scalar subquery

UPDATE tickets SET baggage_kg = baggage_kg + (SELECT MAX(baggage_kg) FROM tickets WHERE passenger_id = 2) WHERE id = 4;

The subquery runs once, before the first row is updated.

Expressions

Kind	Available
Arithmetic	+ - * / % on numbers, `
Comparison and logic	= != < <= > >=, AND, OR, NOT, IS [NOT] NULL (for bool columns). Inequality is !=; <> is not accepted.
Forms	CASE, COALESCE, NULLIF, GREATEST, LEAST, CAST(x AS t), x::t
Numeric functions	ABS, SIGN, CEIL, FLOOR, ROUND, TRUNC, MOD
Text functions	UPPER, LOWER, LENGTH, SUBSTRING, TRIM, LTRIM, RTRIM, POSITION(a IN b), REPLACE, LPAD, RPAD, CONCAT
Date and time	NOW(), CURRENT_DATE, DATE(ts)

Operator precedence follows PostgreSQL.

Types are exact

• There is no implicit conversion. int32 + int64 needs a CAST. A literal takes the type of the column or operand next to it.
• A decimal result of a different scale must be narrowed with ROUND(x, s) or CAST, which round half away from zero.
• Every operation is checked in its own type. A result out of range is refused at that row, naming the operator and the row’s key. Division by zero is refused.
• NULL propagates: SET n = n + 1 leaves a NULL as NULL. Assigning NULL to a NOT NULL column is refused.

The primary key cannot be updated

The primary key is the row’s identity. Assigning it is refused when the statement is compiled, so nothing is written:

kdbs> UPDATE flights SET id = 9 WHERE id = 1;
                         ^
ERR primary-key column 'id' cannot be updated at byte 19; it is the tuple's identity, not a field of it

Remarks

• A WHERE literal of the wrong type for its column is refused when the statement is compiled, before any row is touched, whether or not a row would match:

  kdbs> UPDATE tickets SET baggage_kg = 1 WHERE baggage_kg = '999';
                                                               ^
  ERR column 'baggage_kg' literal at byte 53: expects an integer literal - unquoted, with no fraction
  

• A statement whose expression does not type-check is refused even if no row matches. A bare literal assigned by SET is the exception: it is checked per row, so SET baggage_kg = 'abc' with a WHERE that matches nothing replies UPDATED 0.

• A column assigned twice in one statement is refused.

• An UPDATE never moves a row; every row has a fixed size.

• Assertions are checked by how much the update changes each group; foreign keys by the new parent.

• Reply: UPDATED <n>, the number of rows changed.

Limitations

• Not available in expressions: IN, BETWEEN, LIKE, EXISTS, IS TRUE, boolean literals, correlated subqueries, and subqueries over sys.* views.
• A scalar subquery must return one column and at most one row, and cannot read the row being updated.