Skip to main content
Modifies column values in existing rows. Use a WHERE clause to target specific rows; omitting it updates every row in the table.

​
Syntax

UPDATE [OR conflict_action] table_name
SET column_name = expression [, column_name = expression ...]
[FROM from_clause]
[WHERE expression]
[RETURNING result_column [, ...]]
[ORDER BY sort_expression [, ...]]
[LIMIT expression [OFFSET expression]]

​
Parameters

ParameterDescription
conflict_actionOne of REPLACE, IGNORE, ABORT, ROLLBACK, or FAIL. Controls behavior on constraint violations. Default is ABORT.
table_nameThe table whose rows are to be modified.
column_name = expressionAssigns a new value to a column. The expression can reference the original (pre-update) values of any column in the same row.
from_clauseAdditional tables to join for use in SET expressions and the WHERE clause.
expressionFilter condition. Only rows where this evaluates to true are modified.
result_columnColumn or expression to return for each modified row.
sort_expressionDetermines the order rows are considered when combined with LIMIT.

​
Conflict handling

The optional OR clause specifies how to handle constraint violations during the update.
AlgorithmBehavior
ABORTRoll back the current statement and return an error. The transaction remains active. This is the default.
ROLLBACKRoll back the entire transaction and return an error.
FAILStop at the first constraint violation and return an error, but keep changes made by earlier rows in this statement.
IGNORESkip the row that caused the constraint violation and continue processing remaining rows.
REPLACEDelete the conflicting row before updating the current row. If a NOT NULL constraint fails and there is no DEFAULT value, the statement behaves as ABORT.
UPDATE OR IGNORE employees
SET department = 'Engineering'
WHERE department = 'IT';

​
SET clause

Assigns new values to one or more columns. Each expression sees the original (pre-update) values of all columns in the row. 
UPDATE products
SET price = price * 1.10,
    updated_at = datetime('now');
Multiple columns can also be set using a parenthesized column list: 
UPDATE products
SET (price, updated_at) = (price * 1.10, datetime('now'));

​
FROM clause

Allows the UPDATE to reference other tables. Columns from the joined tables can be used in SET expressions and the WHERE clause. 
UPDATE inventory
SET quantity = inventory.quantity - order_items.qty
FROM order_items
WHERE inventory.product_id = order_items.product_id
  AND order_items.order_id = 1001;
When the target table also appears in the FROM clause, it refers to a second instance of that table for self-joins.

​
WHERE clause

Restricts which rows are updated. Only rows where the expression evaluates to true are modified. If omitted, every row in the table is updated.

​
RETURNING clause

Causes the UPDATE to return the new values of each modified row. 
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 42
RETURNING account_id, balance;
-- Returns: 42|900

​
ORDER BY and LIMIT

ORDER BY determines the order in which rows are considered, and LIMIT caps the number of rows actually modified. ORDER BY requires LIMIT to be present. 
UPDATE logs
SET archived = 1
ORDER BY created_at ASC
LIMIT 1000;
LIMIT accepts an optional OFFSET to skip rows before applying the limit: 
UPDATE logs
SET archived = 1
ORDER BY created_at ASC
LIMIT 1000 OFFSET 500;

​
Examples

​
Update rows matching a condition

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary REAL, department TEXT);
INSERT INTO employees VALUES (1, 'Alice', 90000, 'Engineering');
INSERT INTO employees VALUES (2, 'Bob', 75000, 'Marketing');
INSERT INTO employees VALUES (3, 'Carol', 85000, 'Engineering');

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering';

SELECT name, salary FROM employees WHERE department = 'Engineering';
-- Alice|94500.0
-- Carol|89250.0

​
Update using a join with FROM

CREATE TABLE departments (name TEXT PRIMARY KEY, budget REAL);
INSERT INTO departments VALUES ('Engineering', 500000);
INSERT INTO departments VALUES ('Marketing', 200000);

CREATE TABLE employees2 (id INTEGER PRIMARY KEY, name TEXT, bonus REAL, dept TEXT);
INSERT INTO employees2 VALUES (1, 'Alice', 0, 'Engineering');
INSERT INTO employees2 VALUES (2, 'Bob', 0, 'Marketing');

UPDATE employees2
SET bonus = departments.budget * 0.01
FROM departments
WHERE employees2.dept = departments.name;

SELECT name, bonus FROM employees2;
-- Alice|5000.0
-- Bob|2000.0

​
Update with RETURNING

CREATE TABLE counters (name TEXT PRIMARY KEY, value INTEGER);
INSERT INTO counters VALUES ('page_views', 41);

UPDATE counters
SET value = value + 1
WHERE name = 'page_views'
RETURNING name, value;
-- page_views|42

​
Multiple column update

UPDATE orders
SET status = 'shipped',
    shipped_at = datetime('now'),
    tracking_number = 'TRACK-9981'
WHERE id = 1042;

​
See also

Build docs developers (and LLMs) love

Get started for freeTalk to us