✏️ UPDATE = Change Existing Data
Data changes over time. UPDATE modifies existing rows. Be careful — without WHERE, updates all rows.
📝 Basic UPDATE
-- Update single column UPDATE users SET email = 'newemail@example.com' WHERE id = 123; -- Update multiple columns UPDATE users SET name = 'Alice Updated', age = 31 WHERE id = 123; -- Update with calculation UPDATE products SET price = price * 1.10 WHERE category = 'electronics'; -- Update with condition UPDATE orders SET status = 'shipped' WHERE order_date < NOW() - INTERVAL '1 day'; -- Update from another table (PostgreSQL) UPDATE users SET name = temp.name, email = temp.email FROM temp_users temp WHERE users.id = temp.id;
🎯 Advanced UPDATE
-- Conditional update
UPDATE users
SET status = CASE
WHEN age > 18 THEN 'adult'
WHEN age > 13 THEN 'teen'
ELSE 'child'
END;
-- Update with JOIN (PostgreSQL)
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id AND u.status = 'inactive';
-- Increment counter
UPDATE page_views SET view_count = view_count + 1 WHERE page_url = '/home';
-- Update with subquery
UPDATE users
SET last_order_date = (
SELECT MAX(order_date) FROM orders WHERE user_id = users.id
)
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);
💡 Safety Tips
- Always use WHERE (or you'll update ALL rows!)
- Test with SELECT first (see what will be updated)
- Use transactions for safety (BEGIN; ...; COMMIT;)
- Backup before mass updates
"Forgot WHERE, updated all rows. Disaster. Now always SELECT first, then UPDATE. WHERE is essential."
