MySQL UPDATE Statement | Complete Guide

The MySQL UPDATE statement allows you to change existing data in a table. In this guide, we’ll walk through how the UPDATE command works, when to use it, and the best practices to ensure your updates are accurate and safe.

Basic UPDATE Syntax

The fundamental syntax of the MySQL UPDATE statement is:

# Update specific columns in a table based on a condition
UPDATE table_name
SET 
column1 = value1, 
column2 = value2, ...
WHERE condition;

Components Breakdown

• UPDATE: Keyword that initiates the update operation
• table_name: The table you want to modify
• SET: Specifies which columns to update and their new values
• WHERE: Defines which rows to update (crucial for safety)

Simple UPDATE Examples

Update Single Column

# Update the salary of a specific employee
UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

Update Multiple Columns

# Update salary, department, and last_updated for a specific employee
UPDATE employees
SET 
    salary = 60000, 
    department = 'Engineering', 
    last_updated = NOW()
WHERE employee_id = 101;

Update Using Expressions

# Increase price by 10% for all electronics products
UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';

UPDATE with WHERE Clause

-- Update specific employee
UPDATE employees
SET status = 'active'
WHERE employee_id = 123;

-- Update multiple employees with condition
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'sales' AND hire_date < '2020-01-01';

-- Update with multiple conditions
UPDATE products
SET discount = 10
WHERE category = 'clothing' 
AND price > 50 
AND stock_quantity > 0;

UPDATE with Functions and Expressions

String Functions

# Update email and name for customers created after 2024-01-01
UPDATE customers
SET 
    email = LOWER(email), 
    name = TRIM(name)
WHERE created_date >= '2024-01-01';

Date Functions

# Schedule delivery 7 days after order date for pending orders
UPDATE orders
SET 
    delivery_date = DATE_ADD(order_date, INTERVAL 7 DAY), 
    status = 'scheduled'
WHERE status = 'pending';

Mathematical Operations

# Update price with 15% increase and calculate tax for active products
UPDATE products
SET 
    price = ROUND(price * 1.15, 2), 
    tax_amount = price * 0.08
WHERE active = 1;

Conditional UPDATE with CASE

Use CASE statements for complex conditional logic:

# Update product status based on quantity for electronics category
UPDATE products
SET status = CASE
    WHEN quantity = 0 THEN 'out_of_stock'
    WHEN quantity < 5 THEN 'low_stock'
    ELSE 'in_stock'
END
WHERE category = 'electronics';

Multiple CASE Conditions

# Update bonus and performance_rating based on department and years_of_service
UPDATE employees
SET 
    bonus = CASE
        WHEN department = 'sales' THEN salary * 0.10
        WHEN department = 'engineering' THEN salary * 0.08
        WHEN department = 'marketing' THEN salary * 0.06
        ELSE 0
    END,
    performance_rating = CASE
        WHEN years_of_service > 5 THEN 'senior'
        WHEN years_of_service > 2 THEN 'intermediate'
        ELSE 'junior'
    END;

UPDATE with Subqueries

Using Subqueries in SET Clause

# Update average_rating in products table based on reviews
UPDATE products
SET average_rating = (
    SELECT AVG(rating)
    FROM reviews
    WHERE reviews.product_id = products.product_id
)
WHERE product_id IN (
    SELECT DISTINCT product_id FROM reviews
);

Subquery in WHERE Clause

# Update customers to 'vip' if their total orders since 2024-01-01 exceed 5000
UPDATE customers
SET status = 'vip'
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 5000
);

UPDATE with JOINs

Join multiple tables to update based on related data:

Basic JOIN UPDATE

# Apply 15% discount to orders of premium members
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.discount = 15
WHERE c.membership_type = 'premium';

Multiple Table JOIN

# Update markup_percentage based on category and supplier type
UPDATE products p
JOIN categories cat ON p.category_id = cat.category_id
JOIN suppliers s ON p.supplier_id = s.supplier_id
SET p.markup_percentage = CASE
    WHEN cat.category_name = 'luxury' THEN 50
    WHEN s.supplier_type = 'direct' THEN 30
    ELSE 25
END;

UPDATE with ORDER BY and LIMIT

Control which rows get updated first and limit the number of updates:

-- Update oldest completed orders first
UPDATE orders
SET status = 'archived'
WHERE status = 'completed'
ORDER BY order_date ASC
LIMIT 100;

-- Assign highest priority pending tasks to manager
UPDATE tasks
SET assigned_to = 'manager'
WHERE status = 'pending'
ORDER BY priority DESC, created_date ASC
LIMIT 10;

Multi-table UPDATE

Update multiple tables simultaneously:

# Update total_weight, line_total, and times_ordered for confirmed orders
UPDATE orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
SET 
    o.total_weight = o.total_weight + (oi.quantity * p.weight),
    oi.line_total = oi.quantity * oi.unit_price,
    p.times_ordered = p.times_ordered + oi.quantity
WHERE o.status = 'confirmed';

UPDATE with JSON (MySQL 5.7+)

Updating JSON Values

# Update user theme preference to dark using JSON_SET
UPDATE users
SET preferences = JSON_SET(preferences, '$.theme', 'dark')
WHERE user_id = 123;

JSON Array Operations

# Append 'bestseller' tag to products with top 10 sales_rank
UPDATE products
SET tags = JSON_ARRAY_APPEND(tags, '$', 'bestseller')
WHERE sales_rank <= 10;

Complex JSON Updates

# Update multiple JSON settings for premium users
UPDATE user_profiles
SET settings = JSON_SET(
    settings,
    '$.notifications.email', true,
    '$.privacy.show_profile', false,
    '$.last_updated', NOW()
)
WHERE user_type = 'premium';

Safety Practices

Always Use WHERE Clause

-- DANGEROUS: Updates all rows
UPDATE products 
SET price = 100;

-- SAFE: Updates only specific rows
UPDATE products 
SET price = 100
WHERE category = 'clearance';

Enable Safe Update Mode

# Enable safe updates mode to prevent accidental full-table updates
SET SQL_SAFE_UPDATES = 1;

Use Transactions

# Start a transaction to safely transfer funds between accounts
START TRANSACTION;

UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 1;

UPDATE accounts 
SET balance = balance + 100 
WHERE account_id = 2;

# If everything is correct, save changes
COMMIT;

# If there is an issue, revert changes
ROLLBACK;

Test with SELECT First

-- Test your condition first to avoid accidental updates
SELECT * 
FROM employees 
WHERE department = 'marketing';

-- Then run the update once confirmed
UPDATE employees 
SET salary = salary * 1.05 
WHERE department = 'marketing';

Common UPDATE Patterns

Increment/Decrement Values

# Increment the view count of a specific product
UPDATE products 
SET view_count = view_count + 1 
WHERE product_id = 123;

# Decrement the inventory quantity for a specific product
UPDATE inventory 
SET quantity = quantity - 5 
WHERE product_id = 456;

Toggle Boolean Values

# Toggle the 'active' status of a specific user
UPDATE users
SET active = NOT active
WHERE user_id = 789;

Update Based on Another Column

# Recalculate total_amount for orders with status 'calculating'
UPDATE orders
SET total_amount = subtotal + tax_amount + shipping_cost
WHERE status = 'calculating';

Batch Status Updates

# Update orders to shipped status for west coast warehouse
UPDATE orders
SET 
    status = 'shipped',
    shipped_date = NOW()
WHERE 
    status = 'processing'
    AND warehouse_location = 'west_coast';

Performance Considerations

Use Indexes on WHERE Conditions

# Ensure indexed columns for faster WHERE lookups
CREATE INDEX idx_orders_status 
ON orders(status);

CREATE INDEX idx_products_category 
ON products(category);

Batch Large Updates

# Process large updates in batches to avoid locking the table
UPDATE products
SET last_updated = NOW()
WHERE 
    category = 'books'
    AND last_updated IS NULL
LIMIT 1000;

Monitor Performance

# Check the execution plan for an update query
EXPLAIN 
UPDATE products 
SET price = price * 1.1 
WHERE category = 'electronics';

Error Handling

Common Errors and Solutions

Error 1175: Safe Update Mode

# Solution: Temporarily disable safe updates to run a full update
SET SQL_SAFE_UPDATES = 0;

UPDATE table_name 
SET column = value;

SET SQL_SAFE_UPDATES = 1;

Error 1062: Duplicate Key

# Handle duplicate key violations by ignoring conflicts
UPDATE IGNORE products
SET sku = CONCAT(sku, '_v2')
WHERE category = 'electronics';

Best Practices Summary

1. Always use the WHERE clause unless updating the entire table intentionally
2. Test with SELECT before running UPDATE
3. Use transactions for critical operations
4. Enable safe update mode for protection
5. Create backups before major updates
6. Index WHERE conditions for performance
7. Process large updates in batches
8. Use appropriate data types in SET clauses
9. Validate results after UPDATE operations
10. Document complex UPDATE logic with comments

Also Read: 

How to create tables in MySQL?
Joi database full guide

Conclusion

The MySQL UPDATE statement is a powerful tool for data manipulation. By understanding its various forms, from simple single-column updates to complex multi-table operations with JOINs and subqueries, you can efficiently modify your database records while maintaining data integrity and performance.

Remember to always prioritize safety by using WHERE clauses, testing operations, and implementing proper backup strategies. With these fundamentals mastered, you’ll be able to handle any UPDATE scenario confidently and effectively.

FAQs

# Update salary and status for a specific employee
UPDATE employees
SET 
    salary = 65000, 
    status = 'active'
WHERE employee_id = 101;