Kenhuang Academy
  • English

      • 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