UPDATE

UPDATE modifies existing rows in a table. It names a table, a SET clause describing which columns get which new values, and — critically — a WHERE clause describing which rows should be touched. Everything about UPDATE's safety hinges on that WHERE clause.

Basic UPDATE

Restocking a product

SQL

UPDATE products
SET stock_qty = stock_qty + 50
WHERE sku = 'SKU-1001';

UPDATE 1

Multiple columns can be updated in one statement by separating them with commas in the SET clause.

Updating price and stock together

SQL

UPDATE products
SET unit_price = 27.99,
    stock_qty   = stock_qty + 50
WHERE sku = 'SKU-1001';

An UPDATE without WHERE touches every row in the table

UPDATE products SET unit_price = 0; will happily zero out every price in the table — there is no confirmation prompt. Always write and sanity-check the WHERE clause before running an UPDATE, especially in a production database. Running the equivalent SELECT with the same WHERE clause first (see the tip below) is the standard way to double-check before committing to the change.

Updating from another table

PostgreSQL supports an UPDATE ... FROM pattern for updating rows based on data in another table, which standard SQL does not include in quite this form. The FROM clause brings in a second table to join against, and the join condition typically lives in WHERE.

Marking orders as shipped based on a shipments table

SQL

UPDATE orders
SET status = 'shipped'
FROM shipments
WHERE orders.order_id = shipments.order_id
  AND shipments.shipped_at IS NOT NULL;

A common variant recalculates a value on one table using an aggregate from another — for example, keeping a denormalized order total in sync with its line items.

Recomputing order totals from order_items

SQL

UPDATE orders o
SET total_amount = totals.sum_amount
FROM (
  SELECT order_id, SUM(quantity * unit_price) AS sum_amount
  FROM order_items
  GROUP BY order_id
) AS totals
WHERE o.order_id = totals.order_id;

Every table named in FROM is a join, not a filter

It is easy to forget the join condition when using UPDATE ... FROM, since there is no explicit JOIN keyword. Without a condition tying the FROM table back to the table being updated in WHERE, PostgreSQL pairs every row being updated with every row from the FROM table — producing a cross join and almost certainly the wrong result.

A quick look at RETURNING

Just like INSERT, UPDATE can end with RETURNING to see exactly what values a row ended up with after the change — useful for confirming the update did what was intended, in the same round trip.

Seeing the updated row immediately

SQL

UPDATE products
SET stock_qty = stock_qty + 50
WHERE sku = 'SKU-1001'
RETURNING sku, stock_qty;

 sku      | stock_qty
----------+-----------
 SKU-1001 |       200

Rehearse with SELECT first

Before running an UPDATE with a nontrivial WHERE clause, run the same WHERE clause as a SELECT first: SELECT * FROM products WHERE sku = 'SKU-1001'; If that SELECT returns exactly the rows you expect to change, the UPDATE using the same condition is safe to run.

UPDATE table SET column = value WHERE condition modifies matching rows.
Omitting WHERE updates every row in the table — always double-check it.
UPDATE ... FROM lets one table's update be driven by a join to another table.
RETURNING shows the post-update row values without a separate SELECT.