DML

UPDATE

Q: How do I UPDATE from a SELECT?

Postgres / DuckDB / SQLite: UPDATE target SET col = source.col FROM source WHERE target.id = source.id. MySQL: UPDATE target JOIN source ON ... SET target.col = source.col. SQL Server: UPDATE target SET col = s.col FROM target JOIN source AS s ON .... Fallback for any engine: correlated subquery in the SET clause.

Q: Can I update multiple columns in one statement?

Yes, via comma-separated assignments: UPDATE users SET status = 'active', plan = 'pro', updated_at = NOW() WHERE id = 1. All assignments happen atomically; references to columns on the right side use the pre-update values.

Q: How do I increment a counter?

UPDATE counters SET value = value + 1 WHERE id = 42. The right-hand value reads the pre-update value; the result is stored. Works on every engine. For high-concurrency counters, this is also race-safe inside a transaction.

Q: Can I get back the rows that were updated?

Postgres / SQLite (3.35+) / DuckDB / Oracle: UPDATE ... RETURNING id, old_col, new_col. SQL Server: UPDATE ... OUTPUT DELETED.col AS old_val, INSERTED.col AS new_val. MySQL: no equivalent, so fetch via a separate SELECT after the update.

Q: Can I UPDATE with a LIMIT?

MySQL allows UPDATE ... LIMIT n. Postgres doesn't; wrap in WHERE id IN (SELECT id FROM ... LIMIT n). Useful for batched migrations: update 1000 rows at a time to avoid one huge transaction.

Change values in existing rows. The SET clause says what to change; the WHERE clause says which rows. Forgetting the WHERE clause updates the entire table, and the engine cheerfully obeys.

Syntax #

sql

-- Single-table update.
UPDATE table_name
SET col_1 = expr_1,
    col_2 = expr_2
WHERE condition;

-- Set values from another table (Postgres / DuckDB / SQLite syntax).
UPDATE target
SET   col_x = source.col_x
FROM  source
WHERE target.id = source.id;

Example #

Loading SQL editor...

UPDATE FROM SELECT #

The single most-Googled UPDATE pattern: copying values from one table into another based on a key match. Syntax differs across engines, so pick the right one for yours.

sql

-- Postgres / DuckDB / SQLite.
UPDATE orders
SET   user_email = u.email
FROM  users AS u
WHERE orders.user_id = u.id;

-- MySQL: join syntax inside UPDATE.
UPDATE orders
JOIN  users ON orders.user_id = users.id
SET   orders.user_email = users.email;

-- SQL Server: FROM with the joined source.
UPDATE orders
SET   user_email = u.email
FROM  orders AS o
JOIN  users  AS u ON o.user_id = u.id;

-- Standard SQL fallback (works everywhere): correlated subquery.
UPDATE orders
SET user_email = (SELECT email FROM users WHERE users.id = orders.user_id);

Dialect notes #

RETURNING: Postgres, SQLite (3.35+), DuckDB, Oracle return the updated rows. SQL Server has OUTPUT. MySQL has no equivalent.
LIMIT on UPDATE: MySQL allows UPDATE ... LIMIT n (handy for batched migrations). Postgres requires a WHERE id IN (SELECT id FROM ... LIMIT n) workaround.
Self-reference in SET: UPDATE t SET counter = counter + 1 works everywhere; the right-hand counter reads the pre-update value.
Multi-table updates: MySQL allows updating multiple tables in one statement (UPDATE a JOIN b SET a.x = ..., b.y = ...). Postgres doesn't, so write two statements in a transaction.
BigQuery / Snowflake / Redshift: warehouses support UPDATE, but heavy use is a smell. They're optimized for append-and-rebuild. For analytics workloads, prefer MERGE, INSERT OVERWRITE, or table rebuilds. UPDATE belongs to the OLTP world.

FAQ #

How do I UPDATE from a SELECT?

Postgres / DuckDB / SQLite: UPDATE target SET col = source.col FROM source WHERE target.id = source.id. MySQL: UPDATE target JOIN source ON ... SET target.col = source.col. SQL Server: UPDATE target SET col = s.col FROM target JOIN source AS s ON .... Fallback for any engine: correlated subquery in the SET clause.

Can I update multiple columns in one statement?

Yes, via comma-separated assignments: UPDATE users SET status = 'active', plan = 'pro', updated_at = NOW() WHERE id = 1. All assignments happen atomically; references to columns on the right side use the pre-update values.

How do I increment a counter?

UPDATE counters SET value = value + 1 WHERE id = 42. The right-hand value reads the pre-update value; the result is stored. Works on every engine. For high-concurrency counters, this is also race-safe inside a transaction.

What happens if my UPDATE has no WHERE clause?

Every row in the table is updated. The engine cheerfully obeys. Some clients (MySQL Workbench, DBeaver) refuse this in safe-update mode; most accept it without warning. The defensive habit: wrap risky UPDATE statements in a transaction (BEGIN; UPDATE ...; SELECT ...; COMMIT; or ROLLBACK;).

Can I get back the rows that were updated?

Postgres / SQLite (3.35+) / DuckDB / Oracle: UPDATE ... RETURNING id, old_col, new_col. SQL Server: UPDATE ... OUTPUT DELETED.col AS old_val, INSERTED.col AS new_val. MySQL: no equivalent, so fetch via a separate SELECT after the update.

Can I UPDATE with a LIMIT?

MySQL allows UPDATE ... LIMIT n. Postgres doesn't; wrap in WHERE id IN (SELECT id FROM ... LIMIT n). Useful for batched migrations: update 1000 rows at a time to avoid one huge transaction.

Warning

The pre-flight checklist for any production UPDATE. (1) Run SELECT COUNT(*) FROM ... WHERE <same condition>: does the row count match expectations? (2) Run SELECT col_to_change, new_value FROM ... WHERE <same condition> LIMIT 20: does the change look right on a sample? (3) Wrap the actual UPDATE in a transaction (BEGIN; UPDATE ...; SELECT ...; COMMIT;, or ROLLBACK; if anything looks off). The minute it takes is the cheapest minute in this entire workflow.