UPDATE Data

The UPDATE statement is used to modify existing data in a table. This chapter introduces various uses of the UPDATE statement.

Basic Syntax

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

Update Single Row

-- Update user age
UPDATE users
SET age = 26
WHERE id = 1;

Update Multiple Columns

-- Update multiple columns at once
UPDATE users
SET age = 26, email = 'newemail@example.com'
WHERE id = 1;

Update Multiple Rows

-- Update all users in Beijing
UPDATE users
SET status = 'active'
WHERE city = 'Beijing';

Update with Calculations

-- Increase all product prices by 10%
UPDATE products
SET price = price * 1.1;

-- Increase age by 1
UPDATE users
SET age = age + 1
WHERE id = 1;

Update with Subquery

-- Update user status based on order count
UPDATE users
SET status = 'vip'
WHERE id IN (
    SELECT user_id 
    FROM orders 
    GROUP BY user_id 
    HAVING COUNT(*) > 10
);

Update with JOIN

-- Update based on another table
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.order_date
WHERE o.id = (
    SELECT MAX(id) FROM orders WHERE user_id = u.id
);

Update with CASE

-- Conditional update
UPDATE users
SET status = CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 60 THEN 'adult'
    ELSE 'senior'
END;

Practical Examples

Update User Profile

UPDATE users
SET 
    name = 'Zhang San',
    email = 'zhang@example.com',
    updated_at = NOW()
WHERE id = 1;

Batch Status Update

-- Activate inactive users who logged in recently
UPDATE users
SET status = 'active'
WHERE status = 'inactive'
  AND last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);

Common Errors

Error 1: Forgetting WHERE Clause

-- DANGER: Updates all rows!
UPDATE users SET age = 25;

-- Correct: Use WHERE clause
UPDATE users SET age = 25 WHERE id = 1;

Error 2: Safe Update Mode

-- Disable safe update mode (use carefully)
SET SQL_SAFE_UPDATES = 0;

-- Your update
UPDATE users SET age = 25 WHERE name = 'Zhang San';

-- Re-enable safe update mode
SET SQL_SAFE_UPDATES = 1;

Best Practices

1. Always use WHERE clause
2. Test with SELECT first
3. Use transactions for important updates
4. Backup data before bulk updates
5. Add updated_at timestamp

Performance Tips

-- Create index for WHERE columns
CREATE INDEX idx_status ON users(status);

-- Use LIMIT for batch updates
UPDATE users 
SET status = 'active' 
WHERE status = 'inactive' 
LIMIT 1000;

Summary

• UPDATE ... SET: Modify data
• WHERE clause: Specify which rows to update
• Multiple columns: Update several columns at once
• Calculations: Use expressions in SET
• Subqueries and JOINs: Complex updates

Next Step: Learn DELETE Data