MySQL UPDATE Statement

The UPDATE statement is used to modify existing records in a table. It's crucial to use the WHERE clause to specify which records should be updated. Without a WHERE clause, all records in the table will be updated!

Basic Syntax

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

Update Single Column

-- Update salary for employee with id 1
UPDATE employees 
SET salary = 55000 
WHERE id = 1;

-- Update email for a specific employee
UPDATE employees 
SET email = 'john.new@example.com' 
WHERE employee_id = 101;

Update Multiple Columns

-- Update multiple fields for an employee
UPDATE employees 
SET 
    salary = 60000,
    department = 'Engineering',
    manager_id = 5
WHERE id = 3;

-- Update product information
UPDATE products 
SET 
    price = 49.99,
    stock_quantity = stock_quantity - 1,
    last_updated = NOW()
WHERE product_id = 123;

Update with Calculations

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

-- Apply discount to products in a category
UPDATE products 
SET price = price * 0.9 
WHERE category = 'Electronics';

Update with Subqueries

-- Update based on another table's data
UPDATE employees e
SET e.department_id = (
    SELECT d.department_id 
    FROM departments d 
    WHERE d.department_name = 'Engineering'
)
WHERE e.employee_id = 101;

-- Update using JOIN (MySQL specific syntax)
UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.05
WHERE d.location = 'New York';

Update with CASE Statement

-- Conditional updates based on criteria
UPDATE employees 
SET salary = 
    CASE 
        WHEN performance_rating = 'Excellent' THEN salary * 1.15
        WHEN performance_rating = 'Good' THEN salary * 1.10
        WHEN performance_rating = 'Average' THEN salary * 1.05
        ELSE salary
    END,
    last_review_date = CURDATE()
WHERE review_due = 1;

-- Update product status based on stock
UPDATE products 
SET 
    status = 
        CASE 
            WHEN stock_quantity = 0 THEN 'Out of Stock'
            WHEN stock_quantity < 10 THEN 'Low Stock'
            ELSE 'In Stock'
        END,
    last_stock_check = NOW()
WHERE active = 1;

UPDATE with LIMIT

Limit the number of rows updated (useful for batch processing):

-- Update only 10 oldest pending orders
UPDATE orders 
SET status = 'Processing' 
WHERE status = 'Pending'
ORDER BY order_date ASC
LIMIT 10;

-- Update batch of expired products
UPDATE products 
SET active = 0 
WHERE expiry_date < CURDATE()
LIMIT 100;

Practical Examples

-- Comprehensive employee promotion update
UPDATE employees 
SET 
    job_title = 'Senior ' || job_title,
    salary = 
        CASE 
            WHEN current_job_level = 'Junior' THEN salary * 1.25
            WHEN current_job_level = 'Mid' THEN salary * 1.15
            ELSE salary * 1.10
        END,
    job_level = 
        CASE 
            WHEN current_job_level = 'Junior' THEN 'Mid'
            WHEN current_job_level = 'Mid' THEN 'Senior'
            ELSE 'Lead'
        END,
    promotion_date = CURDATE(),
    updated_by = 'System',
    updated_at = NOW()
WHERE 
    employee_id IN (
        SELECT employee_id 
        FROM performance_reviews 
        WHERE year = 2024 
          AND rating >= 4.5
          AND promotion_eligible = 1
    )
    AND termination_date IS NULL;

-- Seasonal sale price updates
UPDATE products p
JOIN categories c ON p.category_id = c.category_id
LEFT JOIN promotions pr ON p.product_id = pr.product_id 
    AND CURDATE() BETWEEN pr.start_date AND pr.end_date
SET 
    p.sale_price = 
        CASE 
            WHEN pr.promotion_id IS NOT NULL THEN 
                LEAST(p.price * (1 - pr.discount_percent/100), p.price * 0.7)
            WHEN c.seasonal_discount = 1 THEN p.price * 0.8
            ELSE p.price * 0.9
        END,
    p.is_on_sale = 1,
    p.sale_start_date = CURDATE(),
    p.sale_end_date = DATE_ADD(CURDATE(), INTERVAL 30 DAY),
    p.last_price_update = NOW(),
    p.updated_by = 'seasonal_sale_system'
WHERE 
    p.active = 1
    AND p.discontinued = 0
    AND p.stock_quantity > 0
    AND (
        c.category_name IN ('Winter Clothing', 'Holiday Decor', 'Gifts')
        OR EXTRACT(MONTH FROM CURDATE()) IN (11, 12)  -- Nov/Dec
    );

-- Update customer loyalty status
UPDATE customers c
LEFT JOIN (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent,
        MAX(order_date) as last_order_date
    FROM orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id
SET 
    c.loyalty_tier = 
        CASE 
            WHEN o.total_spent >= 10000 THEN 'Platinum'
            WHEN o.total_spent >= 5000 THEN 'Gold'
            WHEN o.total_spent >= 1000 THEN 'Silver'
            ELSE 'Bronze'
        END,
    c.order_count = COALESCE(o.order_count, 0),
    c.total_spent = COALESCE(o.total_spent, 0),
    c.last_order_date = o.last_order_date,
    c.loyalty_points = 
        CASE 
            WHEN o.total_spent >= 10000 THEN FLOOR(o.total_spent / 100) * 2
            WHEN o.total_spent >= 5000 THEN FLOOR(o.total_spent / 100) * 1.5
            ELSE FLOOR(o.total_spent / 100)
        END,
    c.status = 
        CASE 
            WHEN o.last_order_date < DATE_SUB(CURDATE(), INTERVAL 6 MONTH) THEN 'Inactive'
            WHEN o.last_order_date < DATE_SUB(CURDATE(), INTERVAL 3 MONTH) THEN 'At Risk'
            ELSE 'Active'
        END,
    c.updated_at = NOW()
WHERE c.active = 1;

Safety Checklist Before UPDATE

1. Backup your data or work on a test database first
2. Use BEGIN TRANSACTION before critical updates
3. Test your WHERE clause with SELECT first
4. Use LIMIT when updating large tables
5. Add conditions one at a time and test
6. Consider using WHERE conditions on primary/unique keys

Common Mistakes to Avoid

• Forgetting the WHERE clause (updates ALL rows)
• Using incorrect column names
• Not considering NULL values in conditions
• Updating without proper permissions
• Not testing on a small dataset first