Updating Data
1. Updating Single Column
UPDATE customers SET full_name = 'Alice W.'
WHERE id = 42;| Always | Why |
|---|---|
| WHERE | Prevent accidental whole-table updates |
| Indexed predicate | Avoid seq scan + bloat |
2. Updating Multiple Columns
UPDATE customers
SET full_name = 'Alice W.', phone = '+1 555 0100', updated_at = now()
WHERE id = 42;3. Updating with WHERE Condition
UPDATE orders SET status = 'cancelled'
WHERE status = 'new'
AND placed_at < now() - interval '7 days';| Operator | Use |
|---|---|
| IN (...) | Discrete list |
| BETWEEN | Inclusive range |
| EXISTS (subq) | Reference other tables |
4. Updating All Rows
UPDATE settings SET version = version + 1;Warning: An UPDATE without WHERE rewrites every row → bloat. Run inside a transaction and VACUUM afterwards on large tables.
5. Updating from Another Table
UPDATE orders o
SET total_cents = li.total
FROM (SELECT order_id, SUM(price_cents*qty) AS total
FROM order_items GROUP BY order_id) li
WHERE li.order_id = o.id;6. Updating with Subquery
UPDATE customers
SET tier = (SELECT tier FROM tier_rules WHERE customers.spend >= min_spend
ORDER BY min_spend DESC LIMIT 1);7. Using RETURNING Clause
UPDATE orders SET status='shipped', shipped_at=now()
WHERE id = 1001
RETURNING id, status, shipped_at;8. Updating JSON Fields
UPDATE events
SET payload = jsonb_set(payload, '{status}', '"processed"', true)
WHERE id = 7;
UPDATE events SET payload = payload - 'temp_field';
UPDATE events SET payload = payload || '{"v":2}'::jsonb;9. Updating Array Elements
UPDATE posts SET tags = array_append(tags, 'new') WHERE id=1;
UPDATE posts SET tags = array_remove(tags, 'old') WHERE id=1;
UPDATE posts SET tags[2] = 'replacement' WHERE id=1;
UPDATE posts SET tags = array_replace(tags,'old','new') WHERE id=1;10. Updating with Case Expression
UPDATE customers
SET tier = CASE
WHEN spend >= 10000 THEN 'platinum'
WHEN spend >= 1000 THEN 'gold'
ELSE 'standard'
END;| Benefit | Detail |
|---|---|
| One pass | Single table scan vs N UPDATE statements |