⌘K ctrl+k
Search Shortcut cmd + k | ctrl + k
UPDATE Statement

The UPDATE statement modifies the values of rows in a table.

Examples

For every row where i is NULL, set the value to 0 instead:

UPDATE tbl
SET i = 0
WHERE i IS NULL;

Set all values of i to 1 and all values of j to 2:

UPDATE tbl
SET i = 1, j = 2;

Syntax

UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

RETURNING Clause

The RETURNING clause returns the values of the rows that were updated. It uses the same syntax as the SELECT clause, except the DISTINCT modifier is not supported. The returned values reflect the state of the rows after the update.

CREATE TABLE tbl (i INTEGER, j INTEGER);
INSERT INTO tbl VALUES (0, 1), (1, 2);
UPDATE tbl SET j = j + 10 WHERE i = 1 RETURNING *;
i j
1 12

Update from Other Table

A table can be updated based upon values from another table. This can be done by specifying a table in a FROM clause, or using a sub-select statement. Both approaches have the benefit of completing the UPDATE operation in bulk for increased performance.

CREATE OR REPLACE TABLE original AS
    SELECT 1 AS key, 'original value' AS value
    UNION ALL
    SELECT 2 AS key, 'original value 2' AS value;

CREATE OR REPLACE TABLE new AS
    SELECT 1 AS key, 'new value' AS value
    UNION ALL
    SELECT 2 AS key, 'new value 2' AS value;

SELECT *
FROM original;
key value
1 original value
2 original value 2 
UPDATE original
    SET value = new.value
    FROM new
    WHERE original.key = new.key;

Or:

UPDATE original
    SET value = (
        SELECT
            new.value
        FROM new
        WHERE original.key = new.key
    );

SELECT *
FROM original;
key value
1 new value
2 new value 2

Update from Same Table

The only difference between this case and the above is that a different table alias must be specified on both the target table and the source table. In this example AS true_original and AS new are both required.

UPDATE original AS true_original
    SET value = (
        SELECT
            new.value || ' a change!' AS value
        FROM original AS new
        WHERE true_original.key = new.key
    );

Update Using Joins

To select the rows to update, UPDATE statements can use the FROM clause and express joins via the WHERE clause. For example:

CREATE TABLE city (name VARCHAR, revenue BIGINT, country_code VARCHAR);
CREATE TABLE country (code VARCHAR, name VARCHAR);
INSERT INTO city VALUES ('Paris', 700, 'FR'), ('Lyon', 200, 'FR'), ('Brussels', 400, 'BE');
INSERT INTO country VALUES ('FR', 'France'), ('BE', 'Belgium');

To increase the revenue of all cities in France, join the city and the country tables, and filter on the latter:

UPDATE city
SET revenue = revenue + 100
FROM country
WHERE city.country_code = country.code
  AND country.name = 'France';

SELECT *
FROM city;
name revenue country_code
Paris 800 FR
Lyon 300 FR
Brussels 400 BE

Upsert (Insert or Update)

See the Insert documentation for details.

© 2026 DuckDB Foundation, Amsterdam NL
DuckDB Home Code of Conduct Trademark Use Blog