MySQL UPDATE

Overview

The UPDATE statement is used to modify existing records in a table. This chapter covers how to update single or multiple columns, use conditions, and handle updates safely.

UPDATE Syntax

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

Basic UPDATE

Update Single Column

-- Update single column
UPDATE users SET status = 'active' WHERE id = 1;

-- Update multiple columns
UPDATE users SET 
    first_name = 'John',
    last_name = 'Doe',
    email = 'john.doe@example.com'
WHERE id = 1;

Update Multiple Rows

-- Update all rows
UPDATE products SET price = price * 1.1;

-- Update with condition
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

-- Update based on another column
UPDATE products SET final_price = price * (1 - discount_rate / 100);

UPDATE with Conditions

WHERE Clause

-- Update specific row
UPDATE users SET email = 'new@example.com' WHERE id = 5;

-- Update based on value
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;

-- Update with OR condition
UPDATE users SET status = 'vip' WHERE status = 'premium' OR total_orders > 100;

-- Update with IN
UPDATE users SET discount = 20 WHERE id IN (1, 2, 3, 4, 5);

-- Update with subquery
UPDATE orders SET status = 'completed' 
WHERE id IN (SELECT order_id FROM shipments WHERE status = 'delivered');

ORDER BY and LIMIT

-- Update with ORDER BY
UPDATE users SET points = points + 10 
ORDER BY created_at DESC LIMIT 100;

-- Update first 10 inactive users
UPDATE users SET status = 'pending_review' 
WHERE status = 'inactive' 
ORDER BY created_at 
LIMIT 10;

-- Update with ORDER BY and LIMIT (MySQL 8.0+)
UPDATE products SET popularity = popularity + 1 
ORDER BY sales_count DESC 
LIMIT 5;

UPDATE with Expressions

Arithmetic Operations

-- Increment
UPDATE users SET login_count = login_count + 1 WHERE id = 1;

-- Decrement
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- Multiply
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

-- Complex calculation
UPDATE orders SET 
    tax = subtotal * 0.08,
    total = subtotal + (subtotal * 0.08) + shipping_cost
WHERE id = 1;

String Operations

-- Concatenate
UPDATE users SET name = CONCAT(name, ' (Updated)') WHERE id = 1;

-- Replace
UPDATE products SET name = REPLACE(name, 'Old', 'New') WHERE name LIKE '%Old%';

-- Update substring
UPDATE users SET email = CONCAT('user', id, '@example.com') WHERE email IS NULL;

-- Change case
UPDATE users SET name = UPPER(name) WHERE status = 'active';

Date

-- Add interval
UPDATE users SET last_login = DATE_ADD(NOW(), INTERVAL 7 DAY) WHERE id = 1;

-- Set to current timestamp
UPDATE users SET updated_at = NOW() WHERE id = 1;

-- Date arithmetic
UPDATE orders SET shipped_at = DATE(created_at) + INTERVAL 2 DAY WHERE id = 1;

UPDATE with JOIN

Self JOIN

-- Update based on another table
UPDATE users u
INNER JOIN user_stats us ON u.id = us.user_id
SET u.status = 'premium', us.subscription_type = 'premium'
WHERE us.total_spent > 1000;

-- Update using LEFT JOIN
UPDATE products p
LEFT JOIN categories c ON p.category_id = c.id
SET p.category_name = c.name
WHERE p.category_id IS NOT NULL;

Multiple Tables

-- Update from multiple tables
UPDATE orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
SET 
    o.user_name = u.name,
    o.product_name = p.name,
    o.total = p.price * o.quantity
WHERE o.status = 'pending';

Update with Subquery

-- Update with correlated subquery
UPDATE products p
SET price = (
    SELECT AVG(price) FROM products WHERE category_id = p.category_id
)
WHERE p.category_id IN (SELECT id FROM categories WHERE name = 'Electronics');

-- Update with EXISTS
UPDATE users u
SET status = 'active'
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.created_at > '2024-01-01'
);

Conditional UPDATE

CASE Expression

-- Update based on condition
UPDATE users SET 
    status = CASE 
        WHEN points < 100 THEN 'bronze'
        WHEN points < 500 THEN 'silver'
        WHEN points < 1000 THEN 'gold'
        ELSE 'platinum'
    END;

-- Update priority
UPDATE orders SET 
    priority = CASE 
        WHEN total > 1000 THEN 'high'
        WHEN total > 100 THEN 'medium'
        ELSE 'low'
    END,
    updated_at = NOW();

IF Function

-- Simple conditional update
UPDATE users SET 
    status = IF(points > 1000, 'premium', 'regular'),
    updated_at = NOW();

-- Multiple IF
UPDATE products SET 
    stock_status = IF(stock = 0, 'out_of_stock',
                 IF(stock < 10, 'low_stock', 'in_stock'));

Safe UPDATE Practices

Always Use WHERE

-- DANGEROUS: Updates all rows
UPDATE users SET status = 'active';  -- Don't do this!

-- SAFE: With WHERE clause
UPDATE users SET status = 'active' WHERE id = 1;

Backup Before Update

-- Create backup table
CREATE TABLE users_backup AS SELECT * FROM users WHERE id IN (1, 2, 3);

-- Or use transaction (for InnoDB)
START TRANSACTION;
UPDATE users SET status = 'new_status' WHERE id IN (1, 2, 3);
-- Verify changes
-- COMMIT; or ROLLBACK;

Limit Affected Rows

-- Add LIMIT to control affected rows
UPDATE users SET status = 'pending' 
WHERE status = 'inactive' 
LIMIT 100;

-- Use SELECT to verify first
SELECT id, name FROM users WHERE status = 'inactive' LIMIT 100;

UPDATE with RETURNING

MySQL 8.0+ / MySQL 8.0+

-- Get updated values
UPDATE users SET points = points + 100 
WHERE status = 'active'
RETURNING id, name, points;

-- With affected rows
UPDATE orders SET status = 'completed' 
WHERE id = 1
RETURNING id, status, ROW_COUNT() AS affected;

Common Update Patterns

Status Updates

-- Deactivate old records
UPDATE users SET status = 'inactive', 
    deactivated_at = NOW() 
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Approve pending records
UPDATE users SET status = 'active',
    approved_at = NOW()
WHERE status = 'pending' AND approved_by IS NOT NULL;

Counter Updates

-- Increment counter
UPDATE posts SET view_count = view_count + 1 WHERE id = 1;

-- Decrement stock
UPDATE products SET stock = stock - quantity WHERE id IN (1, 2, 3);

-- Reset counters daily
UPDATE daily_stats SET view_count = 0 WHERE stat_date = CURDATE();

Price Updates

-- Apply discount
UPDATE products SET 
    price = ROUND(original_price * (1 - discount_percent / 100), 2),
    discount_end_date = DATE_ADD(NOW(), INTERVAL 7 DAY)
WHERE discount_percent > 0;

-- Increase prices
UPDATE products SET price = ROUND(price * 1.05, 2) 
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics');

Troubleshooting

Common Errors

-- No rows affected
-- Check: WHERE condition, data types

-- Duplicate key error
-- Check: UNIQUE constraints

-- Foreign key constraint error
-- Check: Related tables

-- Data truncation
-- Check: Column sizes, data types

Debug Updates

-- Test with SELECT first
SELECT * FROM users WHERE id = 1;  -- Before update

-- Check affected rows
SELECT ROW_COUNT();

-- Verify changes
SELECT * FROM users WHERE id = 1;  -- After update

Summary

UPDATE statement includes:

• Basic Syntax: SET columns to values
• WHERE Clause: Filter rows to update
• Multiple Columns: Update several columns
• Expressions: Use calculations and functions
• JOIN Updates: Update from multiple tables
• Conditional Updates: CASE, IF for logic
• Safety: Always use WHERE, backup first

---

Previous: WHERE Clause

Next: DELETE