iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

UPDATE

By · Reviewed & last updated · Part of PostgreSQL Tutorial

UPDATE modifies rows that match a WHERE clause. The RETURNING clause is the killer feature — you get the changed rows back in one round trip.

Targeted updates + UPDATE FROM + RETURNING

EXAMPLE
-- Basic
UPDATE users
SET    name = 'Ada Lovelace'
WHERE  id = 1
RETURNING id, name, updated_at;

-- Multiple columns, expression-based
UPDATE products
SET    price = price * 1.10,
       last_priced_at = now()
WHERE  category = 'gadgets';

-- UPDATE FROM — copy from another table
UPDATE posts p
SET    author_email = u.email,
       author_name  = u.name
FROM   users u
WHERE  u.id = p.user_id
  AND  p.author_email IS NULL
RETURNING p.id, p.author_email;

-- Bulk update many rows from a VALUES table
UPDATE inventory inv
SET    stock = data.stock
FROM   (VALUES (1, 100), (2, 50), (3, 25)) AS data(id, stock)
WHERE  inv.id = data.id;

-- ON CONFLICT — upsert via INSERT
INSERT INTO counters (key, value)
VALUES ('signups', 1)
ON CONFLICT (key) DO UPDATE
    SET value = counters.value + 1
RETURNING value;

Why it matters

Always use the parameter-bound form (= \$1) at app level. And always include a WHERE: an UPDATE without it rewrites every row in the table.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
UPDATE users
SET name = 'Ada Lovelace'
WHERE id = 1
RETURNING *;
Try it Yourself »

Discussion

Loading…