PostgreSQL UPDATE Query: Multiple Columns, FROM, LIMIT and RETURNING

If you need to change data that’s already in a table, the PostgreSQL UPDATE statement is the tool: UPDATE products SET price = 34.99 WHERE product_id = 3; changes only the rows the WHERE clause matches.

People get stuck on what’s around it: a forgotten WHERE, a join that matches twice, or LIMIT, which UPDATE doesn’t support.

In this tutorial, I’ll walk you through the PostgreSQL UPDATE query on one small store database: multiple columns, another table, the first N rows, RETURNING and the errors you’ll hit.

The sample products and orders tables we’ll use

Everything runs on PostgreSQL 18 in a demo database, dfq_pgupdate. Here are the columns you’ll need:

-- products: product_id, name, category, price numeric(10,2),
--           stock int (NULL for product 7), last_updated date, specs jsonb
-- orders:   order_id, product_id, customer, state, qty, order_date, status
CREATE TABLE price_updates (product_id int, new_price numeric(10,2));
INSERT INTO price_updates VALUES (5, 119.00), (6, 12.99), (8, 59.99);

Eight products ($14.25 to $149.00) and eight 2026 orders, five Pending. To build your own, see how to create a table in PostgreSQL.

PostgreSQL UPDATE syntax at a glance

UPDATE table_name [AS alias]
SET column1 = value1, column2 = value2
[FROM other_table]
[WHERE condition]
[RETURNING columns];

WHERE works like the WHERE clause in a SELECT. psql reports UPDATE n, the matched rows (even unchanged ones); UPDATE 0 isn’t an error.

Method 1: Update a single row with WHERE (Recommended)

Use this for one known row, ideally by primary key. Same products table: the skillet (product 3) drops from $39.99 to $34.99.

UPDATE products
SET price = 34.99
WHERE product_id = 3;

SELECT product_id, name, price
FROM products
WHERE product_id = 3;

Output:

UPDATE 1
 product_id |       name        | price 
------------+-------------------+-------
          3 | Cast Iron Skillet | 34.99
(1 row)

How does this work?

WHERE finds product 3 and SET writes its new price. I run a SELECT with the same WHERE first.

Method 2: PostgreSQL UPDATE multiple columns at once

You separate the assignments with commas. Here I raise Tools prices 5%, add 10 units and stamp the date:

UPDATE products
SET price = round(price * 1.05, 2),
    stock = stock + 10,
    last_updated = DATE '2026-10-27'
WHERE category = 'Tools';

SELECT product_id, name, price, stock, last_updated
FROM products
WHERE category = 'Tools'
ORDER BY product_id;

Output:

UPDATE 3
 product_id |        name        | price  | stock | last_updated 
------------+--------------------+--------+-------+--------------
          5 | Cordless Drill 20V | 135.45 |    19 | 2026-10-27
          6 | Tape Measure 25 ft |  14.96 |    70 | 2026-10-27
          8 | Socket Set 40 pc   |  57.49 |    25 | 2026-10-27
(3 rows)

How does this work?

Each expression reads the old row, so the drill’s $129.00 becomes $135.45. You can also write SET (price, stock) = (16.99, 45), or use a CASE expression for per-row values.

Method 3: PostgreSQL UPDATE from another table (UPDATE … FROM)

Same products table, now joined to the price_updates list:

UPDATE products AS p
SET price = u.new_price,
    last_updated = DATE '2026-10-27'
FROM price_updates AS u
WHERE p.product_id = u.product_id;

SELECT product_id, name, price, last_updated
FROM products
WHERE category = 'Tools'
ORDER BY product_id;

Output:

UPDATE 3
 product_id |        name        | price  | last_updated 
------------+--------------------+--------+--------------
          5 | Cordless Drill 20V | 119.00 | 2026-10-27
          6 | Tape Measure 25 ft |  12.99 | 2026-10-27
          8 | Socket Set 40 pc   |  59.99 | 2026-10-27
(3 rows)

How does this work?

FROM brings in price_updates and WHERE is the join; unmatched products aren’t touched. More patterns are in my PostgreSQL UPDATE JOIN guide.

What happens when the join matches a row twice?

INSERT INTO price_updates VALUES (8, 52.50);  -- duplicate

UPDATE products AS p SET price = u.new_price
FROM price_updates AS u
WHERE p.product_id = u.product_id
RETURNING p.product_id, p.price;

-- fix: one source row per product
UPDATE products AS p SET price = u.new_price
FROM (SELECT DISTINCT ON (product_id) product_id, new_price
      FROM price_updates ORDER BY product_id, new_price) AS u
WHERE p.product_id = u.product_id
RETURNING p.product_id, p.price;

Output:

INSERT 0 1
 product_id | price  
------------+--------
          5 | 119.00
          6 |  12.99
          8 |  59.99
(3 rows)

UPDATE 3
 product_id | price  
------------+--------
          5 | 119.00
          6 |  12.99
          8 |  52.50
(3 rows)

UPDATE 3

No error: product 8 kept $59.99 on this run, but the docs say the winning row isn’t predictable.

Important: give the join one source row per target. DISTINCT ON keeps the lowest price, so product 8 reliably gets $52.50.

Method 4: Use a subquery in SET

A correlated subquery does the same job as FROM. Same price list, same three products:

UPDATE products AS p
SET price = (SELECT u.new_price FROM price_updates AS u
             WHERE u.product_id = p.product_id)
WHERE EXISTS (SELECT 1 FROM price_updates AS u
              WHERE u.product_id = p.product_id)
RETURNING p.product_id, p.price;

Output:

 product_id | price  
------------+--------
          5 | 119.00
          6 |  12.99
          8 |  59.99
(3 rows)

UPDATE 3

How does this work?

The subquery looks up each product’s new_price. Drop the WHERE EXISTS and the other five products get NULL, because a subquery that finds nothing returns NULL. More on PostgreSQL subqueries.

Method 5: Update with a CTE (WITH … UPDATE)

When the new values need an aggregate, compute them in a WITH query. Same orders table: reserve stock for Pending orders.

WITH pending AS (
  SELECT product_id, sum(qty) AS units
  FROM orders
  WHERE status = 'Pending'
  GROUP BY product_id
)
UPDATE products AS p
SET stock = p.stock - pending.units
FROM pending
WHERE p.product_id = pending.product_id
RETURNING p.product_id, p.stock;

Output:

 product_id | stock 
------------+-------
          1 |     9
          3 |    24
          5 |     7
          6 |    57
(4 rows)

UPDATE 4

How does this work?

The CTE’s GROUP BY returns one total per product, so product 1 (3 Pending units) drops from 12 to 9.

How do you update only the first N rows in PostgreSQL?

There’s no UPDATE ... LIMIT in PostgreSQL. Here’s what you get:

UPDATE orders
SET status = 'Processing'
WHERE status = 'Pending'
LIMIT 2;

Error:

psql:error-update-limit.sql:4: ERROR:  syntax error at or near "LIMIT"
LINE 4: LIMIT 2;
        ^

Pick the rows in a subquery instead. Same orders table: mark the two oldest Pending orders as Processing.

UPDATE orders
SET status = 'Processing'
WHERE order_id IN (
  SELECT order_id
  FROM orders
  WHERE status = 'Pending'
  ORDER BY order_date
  LIMIT 2
)
RETURNING order_id, customer, order_date, status;

Output:

 order_id |  customer   | order_date |   status   
----------+-------------+------------+------------
      102 | Alex Wilber | 2026-10-02 | Processing
      103 | Adele Vance | 2026-10-05 | Processing
(2 rows)

UPDATE 2

How does this work?

The subquery sorts Pending orders by date and keeps IDs 102 and 103; the UPDATE changes only those. Without the ORDER BY, “first” means any two rows.

For big batches, the PostgreSQL 18 docs join a locked CTE on ctid. Repeat it until it reports UPDATE 0.

WITH batch AS (
  SELECT ctid FROM orders
  WHERE status = 'Pending'
  ORDER BY order_date
  LIMIT 2
  FOR UPDATE
)
UPDATE orders AS o
SET status = 'Processing'
FROM batch
WHERE o.ctid = batch.ctid;

PostgreSQL UPDATE RETURNING: see old and new values

RETURNING sends changed rows back. New in PostgreSQL 18, it returns old. and new. values side by side.

UPDATE products
SET price = round(price * 1.05, 2)
WHERE category = 'Kitchen'
RETURNING product_id,
          old.price AS old_price,
          new.price AS new_price,
          new.price - old.price AS change;

Output:

 product_id | old_price | new_price | change 
------------+-----------+-----------+--------
          3 |     39.99 |     41.99 |   2.00
          4 |     64.00 |     67.20 |   3.20
(2 rows)

UPDATE 2

Note: PostgreSQL 17 and older return only new values. INSERT supports RETURNING too.

Handling NULLs in a PostgreSQL UPDATE with COALESCE

UPDATE products SET stock = 0 WHERE stock = NULL;

UPDATE products SET stock = COALESCE(stock, 0) + 20
WHERE stock IS NULL RETURNING product_id, stock;

Output:

UPDATE 0
 product_id | stock 
------------+-------
          7 |    20
(1 row)

UPDATE 1

= NULL matches nothing (UPDATE 0). IS NULL finds the umbrella, and COALESCE(stock, 0) avoids NULL + 20 = NULL.

Update a JSONB field with jsonb_set

UPDATE products
SET specs = jsonb_set(specs, '{color}', '"Black"')
WHERE product_id = 5
RETURNING specs;

Output:

              specs              
---------------------------------
 {"color": "Black", "volts": 20}
(1 row)

UPDATE 1

jsonb_set replaces one key and keeps the rest. The value must be valid JSON, hence '"Black"'. Here’s how JSON and JSONB differ.

Update all rows safely with BEGIN and ROLLBACK

Without WHERE, every row changes, and psql commits right away. Check risky updates inside a transaction (-t hides headers):

BEGIN;

UPDATE products SET price = round(price * 1.10, 2);  -- no WHERE!
SELECT sum(price) AS total_inside_tx FROM products;

ROLLBACK;

SELECT sum(price) AS total_after_rollback FROM products;

Output:

BEGIN
UPDATE 8
          614.35

ROLLBACK
               558.49

The total jumped to $614.35; ROLLBACK restored $558.49. Use the same habit when you delete rows in PostgreSQL.

Common PostgreSQL UPDATE errors and how to fix them

ERROR: column “prise” of relation “products” does not exist

UPDATE products SET prise = 34.99 WHERE product_id = 3;

Error:

psql:error-column-does-not-exist.sql:1: ERROR:  column "prise" of relation "products" does not exist
LINE 1: UPDATE products SET prise = 34.99 WHERE product_id = 3;
                            ^

Fix the spelling or add the column. SET products.price gives the same error.

ERROR: more than one row returned by a subquery used as an expression

UPDATE products
SET price = (SELECT new_price FROM price_updates)
WHERE product_id = 5;

Error:

psql:error-subquery-more-than-one-row.sql:3: ERROR:  more than one row returned by a subquery used as an expression

SET can’t put three prices into one cell. Correlate the subquery (Method 4) or use UPDATE … FROM.

Which PostgreSQL UPDATE method should you use?

You need to…	Use
Change one known row	SET ... WHERE id = ...
Copy values from another table	UPDATE ... FROM
Use totals or counts	WITH … UPDATE
Change only N rows	WHERE id IN (SELECT ... LIMIT n)
See before and after	RETURNING old.*, new.* (18+)

Things to Keep in Mind

• No WHERE means every row: test inside BEGIN and ROLLBACK.
• One UPDATE beats a row-by-row loop (PostgreSQL FOR loop tutorial).
• Duplicate matches in UPDATE … FROM pick an unpredictable row; dedupe the source.
• Index the WHERE and join columns on big tables.

Next, these guides pair well with UPDATE:

• How PostgreSQL joins work
• Stamping rows with NOW()
• Inserting multiple rows at once
• Wrapping updates in a stored procedure

Frequently Asked Questions