PostgreSQL UPDATE

Basic syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

The WHERE clause is optional, but leaving it out updates every row in the table. Always double-check your WHERE clause before running an UPDATE.

Updating a single row

Target a specific row by its primary key:

UPDATE products
SET price = 29.99
WHERE id = 14;

Filtering on the primary key guarantees you're changing exactly one row.

Updating multiple rows

Any rows matching the WHERE condition will be updated. This example marks all peripherals as inactive:

UPDATE products
SET is_active = false
WHERE category = 'peripherals';

Updating multiple columns

List each column assignment separated by commas inside SET:

UPDATE products
SET price = 19.99,
    stock_count = 100,
    category = 'accessories'
WHERE id = 7;

UPDATE with expressions

You can reference the current column value in the SET expression. This is useful for incrementing counters, applying discounts, or transforming text.

-- Reduce all prices by 10%
UPDATE products
SET price = price * 0.90
WHERE category = 'accessories';

-- Increment stock
UPDATE products
SET stock_count = stock_count + 50
WHERE id = 14;

UPDATE ... FROM (PostgreSQL-specific)

PostgreSQL extends the standard UPDATE syntax with a FROM clause, which lets you join another table and use its values in the update. This is PostgreSQL-specific syntax and will not work in SQLite.

UPDATE products
SET price = price * (1 - discounts.pct / 100.0)
FROM discounts
WHERE products.category = discounts.category;

A more practical example: update order totals based on a separate shipping fees table.

UPDATE orders
SET total = orders.total + shipping_rates.fee
FROM shipping_rates
WHERE orders.shipping_region = shipping_rates.region
  AND orders.status = 'pending';

The FROM clause works like a JOIN. You can reference columns from the joined table in both SET and WHERE.

UPDATE with subquery in WHERE

Use a subquery to update rows based on data from another table:

-- Cancel all orders that contain an out-of-stock product
UPDATE orders
SET status = 'cancelled'
WHERE id IN (
    SELECT DISTINCT order_id
    FROM order_items
    JOIN products ON order_items.product_id = products.id
    WHERE products.stock_count = 0
);

The subquery runs first and returns a set of IDs. The outer UPDATE then applies to only those rows.

RETURNING clause

RETURNING makes UPDATE return the affected rows, similar to a SELECT. This is useful when you need the updated values without running a second query.

UPDATE products
SET stock_count = stock_count - 1
WHERE id = 14
RETURNING id, name, stock_count;

You can return any column, including ones you didn't change:

UPDATE orders
SET status = 'shipped'
WHERE status = 'processing'
RETURNING id, status, updated_at;

RETURNING * returns all columns.

Note: RETURNING is PostgreSQL-specific. It is not standard SQL and is not available in MySQL. SQLite supports it from version 3.35 (2021).

The "update all rows" danger

UPDATE without a WHERE clause changes every row in the table:

-- This sets ALL products to inactive
UPDATE products SET is_active = false;

This is rarely what you want. Before running any UPDATE, check what rows you're targeting by running the equivalent SELECT first:

-- Run this first to preview affected rows
SELECT * FROM products WHERE category = 'peripherals';

-- Then run the update if the result looks right
UPDATE products SET is_active = false WHERE category = 'peripherals';

If you're working in production, wrap the UPDATE in a transaction so you can roll it back if something looks wrong:

BEGIN;

UPDATE products SET price = 0 WHERE category = 'accessories';

-- Verify before committing
SELECT name, price FROM products WHERE category = 'accessories';

-- If it looks wrong:
ROLLBACK;

-- If it looks right:
COMMIT;

Checking affected rows

PostgreSQL reports the number of rows updated after each UPDATE statement:

UPDATE 5

In application code, the row count is available through your database driver. In psql, you see it directly in the output.

The RETURNING clause is useful when you need more than a count. For example, after updating an order status, you might want to return the order ID and timestamp to confirm which rows changed:

UPDATE orders
SET status = 'processing',
    updated_at = now()
WHERE status = 'pending'
  AND created_at < now() - interval '1 hour'
RETURNING id, updated_at;

If RETURNING gives back zero rows, no rows matched the WHERE condition.

Common errors

NOT NULL violation

ERROR: null value in column "name" of relation "products" violates not-null constraint

You tried to set a NOT NULL column to NULL. Check your SET clause and confirm the value you're assigning is not null.

Unique constraint violation

ERROR: duplicate key value violates unique constraint "products_sku_key"

The new value conflicts with an existing row. Either find the conflicting row first, or use ON CONFLICT with an INSERT ... ON CONFLICT DO UPDATE (upsert) pattern instead.

Type mismatch

ERROR: invalid input syntax for type integer: "twenty"

The value you're assigning doesn't match the column's data type. Cast explicitly if needed:

UPDATE products
SET price = '29.99'::numeric
WHERE id = 14;

Quick reference

| Syntax | Description | |--------|-------------| | UPDATE t SET col = val WHERE ... | Basic update | | UPDATE t SET col1 = v1, col2 = v2 WHERE ... | Update multiple columns | | UPDATE t SET col = col + 1 WHERE ... | Update using an expression | | UPDATE t SET col = val FROM other WHERE ... | Update using a joined table (PostgreSQL) | | UPDATE t SET col = val WHERE id IN (SELECT ...) | Update using a subquery | | UPDATE t SET col = val WHERE ... RETURNING * | Return updated rows | | UPDATE t SET col = val | Update all rows (use with caution) |