SQL UPDATE

-- Single row
UPDATE users
SET name = 'Ada Lovelace'
WHERE id = 1;

-- Multiple columns at once
UPDATE orders
SET status = 'paid',
    paid_at = NOW(),
    updated_at = NOW()
WHERE id = 42;

-- Use a transaction for multi-step updates
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Conditional update with CASE
UPDATE products
SET price = CASE
  WHEN category = 'book'     THEN price * 0.9
  WHEN category = 'software' THEN price * 0.8
  ELSE price
END;

-- UPDATE from another table (Postgres syntax)
UPDATE users u
SET tier = t.tier
FROM tiers t
WHERE u.id = t.user_id
  AND u.tier IS NULL;

-- LIMIT updates (MySQL extension) - cap rows changed
-- Postgres equivalent uses a CTE with row_number()

-- RETURNING - see what changed (Postgres)
UPDATE orders
SET status = 'cancelled'
WHERE created_at < NOW() - INTERVAL '30 days'
  AND status = 'open'
RETURNING id, user_id;

-- The forever bug: forgotten WHERE
UPDATE users SET tier = 'free';            -- updates EVERY user
-- Always run a SELECT first to confirm what you'll change:
SELECT id, email, tier FROM users WHERE id = 1;

-- Defensive pattern - target a known small set
UPDATE orders
SET status = 'shipped'
WHERE id IN (SELECT id FROM orders_to_ship LIMIT 100)
  AND status = 'paid';

-- Audit columns
UPDATE users
SET name = 'Ada', updated_at = NOW(), updated_by = current_user
WHERE id = 1;

UPDATE customers
SET email = 'new@example.com'
WHERE id = 42;