UPDATE and DELETE Rows in PostgreSQL

UPDATE modifies existing rows, and DELETE removes them. Both commands should almost always include a WHERE clause to avoid affecting every row in the table.

Updating Rows

-- Update a single column
UPDATE customers
SET email = 'newemail@example.com'
WHERE id = 42;

-- Update multiple columns
UPDATE products
SET price = 39.99, stock = stock - 1
WHERE id = 101;

-- Update with a subquery
UPDATE orders
SET status = 'shipped'
WHERE id IN (
  SELECT order_id FROM shipments WHERE shipped_at IS NOT NULL
);

The SET clause assigns new values. You can reference the current column value (like stock - 1) or use subqueries to derive values from other tables.

Deleting Rows

-- Delete specific rows
DELETE FROM sessions
WHERE expires_at < NOW();

-- Delete with a subquery
DELETE FROM order_items
WHERE order_id IN (
  SELECT id FROM orders WHERE status = 'cancelled'
);

Using RETURNING with UPDATE and DELETE

-- See which rows were updated
UPDATE products
SET price = price * 0.9
WHERE category = 'Clearance'
RETURNING id, name, price;

-- See which rows were deleted
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id, user_id;

RETURNING gives you the affected rows immediately, which is useful for logging, auditing, or cascading operations in application code.

Safety Tips

• Always test with a SELECT using the same WHERE clause before running UPDATE or DELETE
• Wrap destructive operations in a transaction so you can ROLLBACK if needed
• Use RETURNING to verify the affected rows match your expectations
• Consider soft deletes (a deleted_at timestamp) instead of permanent deletion