What Is UPDATE?

UPDATE employees
SET salary = 85000
WHERE id = 1;

Basic Syntax

-- Update a single column
UPDATE employees
SET email = 'john.doe@company.com'
WHERE id = 101;

-- Update multiple columns
UPDATE employees
SET
    first_name = 'Jonathan',
    last_name = 'Smith',
    email = 'jonathan.smith@email.com',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1234;

Update with Expressions

SET clauses accept expressions, not just literal values. This lets you compute new values from existing data.

-- Give all Engineering employees a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

-- Update using CASE expression
UPDATE products
SET status = CASE
    WHEN stock_quantity = 0 THEN 'Out of Stock'
    WHEN stock_quantity < 10 THEN 'Low Stock'
    ELSE 'In Stock'
END
WHERE category_id = 5;

-- Update using a subquery
UPDATE employees
SET salary = (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = 3
)
WHERE department_id = 3 AND performance_rating = 'Average';

Safe Update Pattern

Follow a three-step process to avoid accidental data loss: preview, execute, verify.

-- Step 1: Preview affected rows
SELECT * FROM employees WHERE department = 'Engineering';

-- Step 2: Run the update
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

-- Step 3: Verify the changes
SELECT * FROM employees WHERE department = 'Engineering';

BEGIN TRANSACTION;

UPDATE employees
SET status = 'Active'
WHERE last_login_date > DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY);

-- Check affected rows
SELECT ROW_COUNT();

-- COMMIT;   -- if satisfied
-- ROLLBACK; -- if something went wrong

Batch Updates

For large datasets, update in batches to avoid long locks and transaction log bloat.

-- Batch 1
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales'
AND employee_id BETWEEN 1000 AND 2000;

-- Batch 2
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales'
AND employee_id BETWEEN 2001 AND 3000;

-- Repeat until all rows are processed

Common Pitfalls

-- Dangerous: Updates ALL rows
UPDATE employees SET salary = 50000;

-- Safe: Targeted update with WHERE
UPDATE employees SET salary = 50000 WHERE department = 'Interns';

Performance Considerations

1. Index the WHERE columns — speeds up row lookup
2. Batch large updates — reduces lock duration
3. Monitor transaction logs — UPDATE generates redo entries
4. Use EXPLAIN — verify the query plan before updating
5. Minimize column count — only update columns that change

-- Check the execution plan
EXPLAIN UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

Practice Exercises

Exercise 1: Write an UPDATE to set all orders older than 30 days to status 'Archived'.

-- Solution
UPDATE orders
SET status = 'Archived'
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

Exercise 2: Update multiple columns for a specific employee.

-- Solution
UPDATE employees
SET
    first_name = 'Robert',
    last_name = 'Brown',
    email = 'robert.brown@company.com',
    department_id = 5
WHERE employee_id = 456;

Exercise 3: Use a CASE expression to assign discounts based on price.

-- Solution
UPDATE products
SET discount_percentage = CASE
    WHEN price > 100 THEN 15
    WHEN price > 50 THEN 10
    ELSE 5
END
WHERE category = 'Clothing';

Summary