SQL UPDATE — One Missing WHERE Suspended All Users

-- io.thecodeforge — Database tutorial

-- Scenario: An e-commerce platform's user service needs to update
-- a customer's email address after they changed it in account settings.
-- This is the most basic UPDATE pattern — one row, multiple columns.

-- BEFORE: Always check what you're about to change.
-- Run this SELECT first using the same WHERE clause you plan to use in UPDATE.
-- If the SELECT returns the right rows, your UPDATE will hit the same rows.
SELECT
    user_id,
    email,
    display_name,
    updated_at
FROM users
WHERE user_id = 4821;
-- Output shows exactly one row. Good. Now we know the WHERE clause is precise.

-- NOW: Run the actual UPDATE.
-- SET lists only the columns that are changing — email, display_name, updated_at.
-- Every other column (password_hash, created_at, etc.) is left completely alone.
UPDATE users
SET
    email        = 'sarah.okonkwo@newdomain.com',  -- new email from the form
    display_name = 'Sarah O.',                      -- user also updated their display name
    updated_at   = NOW()                            -- always track when a row was last touched
WHERE
    user_id = 4821;  -- PRIMARY KEY filter — this can only ever match one row

-- Verify the change landed correctly.
-- Never assume an UPDATE worked — always read back the affected row.
SELECT
    user_id,
    email,
    display_name,
    updated_at
FROM users
WHERE user_id = 4821;

-- io.thecodeforge — Database tutorial

-- Scenario: A fulfilment service needs to mark all orders from a specific
-- warehouse as 'on_hold' because that warehouse just lost power.
-- This is a bulk UPDATE — multiple rows affected — so we wrap it in a transaction
-- and audit the row count before committing.

-- Step 1: Start the transaction. Nothing is permanent until COMMIT.
BEGIN;

-- Step 2: Count exactly how many rows your WHERE clause will touch.
-- Run this before the UPDATE. Read the number. Does it match your expectation?
SELECT COUNT(*)
FROM orders
WHERE
    warehouse_id   = 'WH-DALLAS-03'  -- only orders from the affected warehouse
    AND order_status = 'processing'   -- only orders that haven't shipped yet
    AND created_at >= '2025-01-17';   -- only today's orders — don't touch historical data
-- Expected: ~80 rows. If this returns 80,000, STOP HERE. ROLLBACK. Re-examine the query.

-- Step 3: Run the UPDATE inside the same transaction.
-- Because we're inside BEGIN, this change is reversible until we hit COMMIT.
UPDATE orders
SET
    order_status   = 'on_hold',                          -- new status reflecting warehouse outage
    status_reason  = 'Warehouse WH-DALLAS-03 power outage', -- human-readable audit trail
    updated_at     = NOW(),                              -- timestamp the change
    updated_by     = 'ops_automation_service'            -- which system/user made this change
WHERE
    warehouse_id   = 'WH-DALLAS-03'
    AND order_status = 'processing'
    AND created_at >= '2025-01-17';
-- The database returns: UPDATE 84
-- 84 rows updated. This matched our expectation. Good.

-- Step 4: Inspect a sample of the updated rows before committing.
-- These will show the updated values because we're still inside the transaction.
SELECT order_id, order_status, status_reason, updated_at
FROM orders
WHERE warehouse_id = 'WH-DALLAS-03'
  AND order_status = 'on_hold'
  AND created_at >= '2025-01-17'
LIMIT 5;

-- Step 5: Only commit once you've confirmed the data looks correct.
-- Replace COMMIT with ROLLBACK if anything looks wrong — zero data is lost.
COMMIT;
-- After COMMIT: all 84 rows are permanently updated and visible to every connection.

-- io.thecodeforge — Database tutorial

-- Scenario: A SaaS billing service processes monthly renewals.
-- When a payment succeeds, the payments table is updated first.
-- We then need to sync the subscription_tier in user_profiles
-- to match what the customer actually paid for this month.
-- The driving data (new tier) lives in payments — the rows to update live in user_profiles.

-- ── APPROACH 1: UPDATE with FROM + JOIN (PostgreSQL syntax) ──────────────────
-- Preferred: readable, and the query planner handles this efficiently.

UPDATE user_profiles AS up          -- alias the target table for readability
SET
    subscription_tier = p.new_tier,           -- take the tier from the payments row
    tier_valid_until  = p.next_billing_date,  -- set expiry from billing cycle in payments
    updated_at        = NOW()
FROM payments AS p                            -- this is the source of truth for the new tier
WHERE
    up.user_id    = p.user_id                 -- join condition: link profiles to their payment
    AND p.payment_status = 'succeeded'        -- only sync rows where payment actually went through
    AND p.billing_cycle  = '2025-01'          -- only this month's billing run — never re-process old data
    AND p.processed_at  >= NOW() - INTERVAL '1 hour'; -- safety window: only the last hour's payments
-- This updates ONLY the profile rows for users who just paid successfully.


-- ── APPROACH 2: Correlated Subquery (works on MySQL, SQLite, and everywhere else) ──
-- Use this when you need cross-database compatibility.
-- Slower at scale — always EXPLAIN this before running on large tables.

UPDATE user_profiles
SET
    subscription_tier = (
        -- This subquery runs once per row in user_profiles that matches the outer WHERE.
        -- It fetches the new_tier from the corresponding payment record.
        SELECT p.new_tier
        FROM payments AS p
        WHERE
            p.user_id        = user_profiles.user_id  -- correlated: links back to the outer row
            AND p.payment_status = 'succeeded'
            AND p.billing_cycle  = '2025-01'
        ORDER BY p.processed_at DESC
        LIMIT 1  -- defensive: take the most recent payment if somehow there are duplicates
    ),
    updated_at = NOW()
WHERE
    -- Only attempt the update for users who actually have a matching payment record.
    -- Without this EXISTS check, users with no payment this month get NULL written to subscription_tier.
    EXISTS (
        SELECT 1
        FROM payments AS p
        WHERE
            p.user_id        = user_profiles.user_id
            AND p.payment_status = 'succeeded'
            AND p.billing_cycle  = '2025-01'
    );

-- io.thecodeforge — Database tutorial

-- Scenario: A checkout service needs to decrement inventory when an item is added to cart.
-- Two problems to solve:
--   1. We need the updated stock level immediately (for the API response) — no second SELECT.
--   2. We must never go below zero — this is a safety constraint.
-- RETURNING solves problem 1. A WHERE guard solves problem 2.

-- ── PATTERN: Atomic decrement with RETURNING (PostgreSQL) ────────────────────
-- This is the pattern inside every inventory and rate-limiter service.
-- The whole thing is one round-trip. No race condition between UPDATE and SELECT.

UPDATE product_inventory
SET
    quantity_available = quantity_available - 1,  -- decrement by exactly 1 unit
    quantity_reserved  = quantity_reserved  + 1,  -- track how many are in-cart but not purchased
    last_reserved_at   = NOW()
WHERE
    product_id         = 'SKU-00441'              -- specific product
    AND quantity_available > 0                    -- CRITICAL: never decrement below zero
                                                  -- If stock is 0, this WHERE fails to match,
                                                  -- UPDATE touches 0 rows, and RETURNING returns nothing.
                                                  -- The application reads empty result = out of stock.
RETURNING
    product_id,
    quantity_available,   -- this is the UPDATED value, already decremented
    quantity_reserved,    -- already incremented
    last_reserved_at;
    -- No second SELECT needed. This is the post-update state.


-- ── PATTERN: Audit trail with a shadow table (PostgreSQL trigger approach) ────
-- For compliance systems: every UPDATE writes a before-image to an audit log.
-- This is the database-layer safety net that catches admin tool changes
-- and migration-script changes that never touch your app logs.

-- First, the audit table structure:
CREATE TABLE IF NOT EXISTS user_profiles_audit (
    audit_id        BIGSERIAL PRIMARY KEY,
    user_id         INT          NOT NULL,
    changed_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    changed_by      TEXT         NOT NULL,  -- application user or DB user
    operation       TEXT         NOT NULL,  -- 'UPDATE', 'DELETE'
    old_email       TEXT,                   -- value BEFORE the change
    new_email       TEXT,                   -- value AFTER the change
    old_tier        TEXT,
    new_tier        TEXT
);

-- The trigger fires automatically after every UPDATE on user_profiles.
-- You don't call this manually — the database calls it.
CREATE OR REPLACE FUNCTION log_user_profile_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- OLD = the row values before the UPDATE
    -- NEW = the row values after the UPDATE
    INSERT INTO user_profiles_audit (
        user_id, changed_by, operation, old_email, new_email, old_tier, new_tier
    ) VALUES (
        OLD.user_id,
        current_user,      -- the DB role that ran the UPDATE
        'UPDATE',
        OLD.email,         -- what it was before
        NEW.email,         -- what it became
        OLD.subscription_tier,
        NEW.subscription_tier
    );
    RETURN NEW;  -- required: return the new row to allow the UPDATE to proceed
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_user_profile_audit
    AFTER UPDATE ON user_profiles       -- fires after every UPDATE, not before
    FOR EACH ROW                        -- once per affected row, not once per statement
    EXECUTE FUNCTION log_user_profile_changes();