UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

-- Update a single column
UPDATE employees
SET email = 'alice.smith@newdomain.com'
WHERE employee_id = 101;

-- Update multiple columns at once
UPDATE employees
SET last_name  = 'Johnson',
    email      = 'alice.johnson@example.com',
    updated_at = NOW()
WHERE employee_id = 101;

-- Swap two column values atomically
UPDATE products
SET price = cost,
    cost  = price
WHERE product_id = 55;

-- Increment a counter column
UPDATE page_views
SET view_count = view_count + 1
WHERE page_slug = '/home';

-- Apply a bulk percentage price increase
UPDATE products
SET price = ROUND(price * 1.05, 2)
WHERE category_id = 3;

-- Conditional update using expressions
UPDATE orders
SET discount = CASE
  WHEN total > 1000 THEN 0.10
  WHEN total > 500  THEN 0.05
  ELSE 0
END
WHERE status = 'pending';

-- DANGEROUS: updates all 50 000 employee records
UPDATE employees SET salary = 50000;

-- Safe pattern: SELECT first, then UPDATE with the same WHERE
SELECT COUNT(*) FROM employees WHERE department_id = 5;
UPDATE employees SET salary = 55000 WHERE department_id = 5;

-- Enable safe update mode for the current session
SET SESSION sql_safe_updates = 1;

-- This now errors because there is no key-column WHERE clause
UPDATE employees SET status = 'active';
-- ERROR 1175: You are using safe update mode

-- A WHERE on an indexed column (e.g. primary key) is allowed
UPDATE employees SET status = 'active' WHERE employee_id > 0;

-- Disable when you intentionally need a full-table update
SET SESSION sql_safe_updates = 0;

-- Sync the location field on employees based on their department
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.location = d.city
WHERE d.region = 'EMEA';

-- Give a 10% raise to everyone in the Engineering department
UPDATE employees e
INNER JOIN departments d ON e.department_id = d.id
SET e.salary = ROUND(e.salary * 1.10, 2)
WHERE d.name = 'Engineering';

-- Practical example: update product prices from a reference/pricing table
UPDATE products p
JOIN pricing_overrides po ON po.product_id = p.id
SET p.price      = po.new_price,
    p.updated_at = NOW()
WHERE po.effective_date <= CURDATE()
  AND po.applied = 0;

-- Mark the overrides as applied in the same query
UPDATE products p
JOIN pricing_overrides po ON po.product_id = p.id
SET p.price       = po.new_price,
    p.updated_at  = NOW(),
    po.applied    = 1,
    po.applied_at = NOW()
WHERE po.effective_date <= CURDATE()
  AND po.applied = 0;

-- Recalculate each order's total from its line items
UPDATE orders o
SET o.total = (
  SELECT SUM(unit_price * quantity)
  FROM order_items
  WHERE order_id = o.id
);

-- Set a flag on orders that contain a specific product
UPDATE orders
SET has_premium = 1
WHERE id IN (
  SELECT DISTINCT order_id
  FROM order_items
  WHERE product_id = 99
);

-- This FAILS in MySQL:
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id IN (
  SELECT employee_id FROM employees WHERE department_id = 2
);
-- ERROR 1093

-- Fix: wrap in a derived table
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id IN (
  SELECT employee_id FROM (
    SELECT employee_id FROM employees WHERE department_id = 2
  ) AS sub
);

-- Batch update: process 1000 rows at a time
UPDATE notifications
SET sent = 1
WHERE sent = 0
ORDER BY created_at
LIMIT 1000;

#!/bin/bash
# Keep updating until fewer than 1000 rows are affected
while true; do
  ROWS=$(mysql mydb -sN -e "
    UPDATE notifications SET sent=1
    WHERE sent=0 ORDER BY created_at LIMIT 1000;
    SELECT ROW_COUNT();
  ")
  echo "Updated ${ROWS} rows"
  [ "${ROWS}" -lt 1000 ] && break
  sleep 0.1
done

-- Assign tier labels based on total spending in one query
UPDATE customers
SET tier = CASE
  WHEN lifetime_spend >= 10000 THEN 'platinum'
  WHEN lifetime_spend >= 5000  THEN 'gold'
  WHEN lifetime_spend >= 1000  THEN 'silver'
  ELSE                              'bronze'
END;

-- Bulk status update for specific order IDs
UPDATE orders
SET status = CASE id
  WHEN 1001 THEN 'shipped'
  WHEN 1002 THEN 'delivered'
  WHEN 1003 THEN 'cancelled'
END
WHERE id IN (1001, 1002, 1003);

-- Table with a version column
CREATE TABLE inventory (
  product_id INT UNSIGNED PRIMARY KEY,
  quantity   INT NOT NULL DEFAULT 0,
  version    INT NOT NULL DEFAULT 0
);

-- Read current state (application stores version = 3)
SELECT product_id, quantity, version
FROM inventory
WHERE product_id = 42;
-- Returns: quantity=100, version=3

-- Update: only succeeds if version hasn't changed since we read it
UPDATE inventory
SET quantity = 95,
    version  = version + 1      -- increment version
WHERE product_id = 42
  AND version    = 3;            -- must match what we read

-- Check result
SELECT ROW_COUNT() AS rows_affected;
-- 1 = success, version is now 4
-- 0 = someone else already updated it; application should re-read and retry

-- BEFORE UPDATE: prevent negative inventory
CREATE TRIGGER trg_inventory_before_update
BEFORE UPDATE ON inventory
FOR EACH ROW
BEGIN
  IF NEW.quantity < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Inventory quantity cannot be negative';
  END IF;
END;

-- AFTER UPDATE: write an audit trail
CREATE TRIGGER trg_employees_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_audit
    (employee_id, field_changed, old_value, new_value, changed_at, changed_by)
  VALUES
    (OLD.id, 'salary', OLD.salary, NEW.salary, NOW(), CURRENT_USER());
END;

-- ROW_COUNT() returns the number of rows actually CHANGED
UPDATE products SET active = 0 WHERE expires_at < NOW();
SELECT ROW_COUNT() AS rows_deactivated;

-- Important: rows that matched but were already at the new value count as 0
UPDATE users SET status = 'active' WHERE status = 'active';
SELECT ROW_COUNT();
-- Returns 0 (no rows changed value, even if many matched)

-- Wrap multiple updates in a transaction for atomicity
START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1001;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2001;

-- Verify balances look correct before committing
SELECT account_id, balance
FROM accounts
WHERE account_id IN (1001, 2001);

-- If everything looks good:
COMMIT;

-- If something is wrong:
-- ROLLBACK;

-- Session A: UPDATE acquires X locks on matching rows
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Row lock held on account_id=1 until COMMIT or ROLLBACK

-- Session B: trying to UPDATE the same row will WAIT
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
-- Blocked until Session A commits or times out (innodb_lock_wait_timeout)

-- See what is locked right now
SELECT r.trx_id AS waiting_trx,
       b.trx_id AS blocking_trx,
       b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

-- Add an auto-updating timestamp to any table
ALTER TABLE products
  ADD COLUMN updated_at DATETIME
    NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP;

-- Now every UPDATE automatically sets updated_at = NOW()
UPDATE products SET price = 29.99 WHERE id = 5;
SELECT id, price, updated_at FROM products WHERE id = 5;
-- updated_at is set to the current timestamp automatically

-- Find all rows updated in the last 24 hours
SELECT * FROM products WHERE updated_at >= NOW() - INTERVAL 1 DAY;

-- Run EXPLAIN on an UPDATE to check index usage before executing on large tables
EXPLAIN UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Look for type=range (index used) vs type=ALL (full table scan -- add an index!)
-- Add a composite index to cover this common update pattern:
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- Re-check after adding the index
EXPLAIN UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- type: range   key: idx_status_created   (much faster)

-- Scenario: developer intends to update one user's email
-- but forgets to add the WHERE clause

-- WRONG: updates ALL 500,000 users
UPDATE users SET email = 'wrong@example.com';

-- After executing this by mistake:
-- 1. Check if you are inside an open transaction (still time to ROLLBACK)
SELECT @@autocommit;   -- if 0, you may still be in a transaction
ROLLBACK;              -- attempt rollback immediately

-- 2. If autocommit was ON, the change is committed. You need binary log recovery.
-- Check if binary logging is enabled:
SHOW VARIABLES LIKE 'log_bin';

-- 3. Prevention: enable sql_safe_updates
SET SESSION sql_safe_updates = 1;
-- Now this will error: UPDATE users SET email = 'x' WHERE email LIKE '%';
-- ERROR 1175: You are using safe update mode