UPDATE Statement
Modify existing data in your tables with the UPDATE statement.
- Single Column Updates β Change one field at a time
- Multi-Column Updates β Modify several fields simultaneously
- Safe Patterns β Preview changes before committing Always preview with SELECT before running UPDATE.
What Is UPDATE?
DfUPDATE Statement
The UPDATE statement modifies existing records in a table. It locates rows using a WHERE condition, then changes column values as specified in the SET clause. Without WHERE, all rows are updated.
UPDATE employees
SET salary = 85000
WHERE id = 1;
Basic Syntax
DfUPDATE Syntax
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
The WHERE clause is optional but strongly recommended.
| Component | Purpose | Required |
|---|---|---|
UPDATE table_name | Specifies the target table | Yes |
SET column = value | Defines columns and new values | Yes |
WHERE condition | Filters which rows to update | No (dangerous) |
-- Update a single column
UPDATE employees
SET email = 'john.doe@company.com'
WHERE id = 101;
-- Update multiple columns
UPDATE employees
SET
first_name = 'Jonathan',
last_name = 'Smith',
email = 'jonathan.smith@email.com',
updated_at = CURRENT_TIMESTAMP
WHERE id = 1234;
Update with Expressions
SET clauses accept expressions, not just literal values. This lets you compute new values from existing data.
DfExpression in SET
A SET expression can reference the current column value, use functions, or combine multiple columns. The expression is evaluated once per matched row.
-- Give all Engineering employees a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';
-- Update using CASE expression
UPDATE products
SET status = CASE
WHEN stock_quantity = 0 THEN 'Out of Stock'
WHEN stock_quantity < 10 THEN 'Low Stock'
ELSE 'In Stock'
END
WHERE category_id = 5;
-- Update using a subquery
UPDATE employees
SET salary = (
SELECT AVG(salary)
FROM employees
WHERE department_id = 3
)
WHERE department_id = 3 AND performance_rating = 'Average';
Safe Update Pattern
Follow a three-step process to avoid accidental data loss: preview, execute, verify.
-- Step 1: Preview affected rows
SELECT * FROM employees WHERE department = 'Engineering';
-- Step 2: Run the update
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';
-- Step 3: Verify the changes
SELECT * FROM employees WHERE department = 'Engineering';
DfTransaction Safety
Wrapping UPDATE in a transaction allows rollback if the result is unexpected. Use BEGIN TRANSACTION, run the UPDATE, inspect the result, then COMMIT or ROLLBACK.
BEGIN TRANSACTION;
UPDATE employees
SET status = 'Active'
WHERE last_login_date > DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY);
-- Check affected rows
SELECT ROW_COUNT();
-- COMMIT; -- if satisfied
-- ROLLBACK; -- if something went wrong
Batch Updates
For large datasets, update in batches to avoid long locks and transaction log bloat.
DfBatch Update
A batch update processes rows in chunks using a range condition (e.g., BETWEEN). This limits lock scope and keeps transaction logs manageable.
-- Batch 1
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales'
AND employee_id BETWEEN 1000 AND 2000;
-- Batch 2
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales'
AND employee_id BETWEEN 2001 AND 3000;
-- Repeat until all rows are processed
Common Pitfalls
| Pitfall | Consequence | Solution |
|---|---|---|
| Missing WHERE clause | Updates all rows | Always add WHERE |
| Forgetting NULL handling | Unexpected NULL values | Use SET col = COALESCE(val, default) |
| Ignoring constraints | Constraint violation error | Check foreign keys before updating |
| No transaction wrap | Cannot undo mistakes | Use BEGIN/COMMIT/ROLLBACK |
-- Dangerous: Updates ALL rows
UPDATE employees SET salary = 50000;
-- Safe: Targeted update with WHERE
UPDATE employees SET salary = 50000 WHERE department = 'Interns';
Performance Considerations
DfUPDATE Performance
UPDATE performance depends on WHERE clause indexing, row count, and transaction logging. Indexed WHERE columns allow the database to locate rows quickly without full table scans.
- Index the WHERE columns β speeds up row lookup
- Batch large updates β reduces lock duration
- Monitor transaction logs β UPDATE generates redo entries
- Use EXPLAIN β verify the query plan before updating
- Minimize column count β only update columns that change
-- Check the execution plan
EXPLAIN UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';
Practice Exercises
Exercise 1: Write an UPDATE to set all orders older than 30 days to status 'Archived'.
-- Solution
UPDATE orders
SET status = 'Archived'
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);
Exercise 2: Update multiple columns for a specific employee.
-- Solution
UPDATE employees
SET
first_name = 'Robert',
last_name = 'Brown',
email = 'robert.brown@company.com',
department_id = 5
WHERE employee_id = 456;
Exercise 3: Use a CASE expression to assign discounts based on price.
-- Solution
UPDATE products
SET discount_percentage = CASE
WHEN price > 100 THEN 15
WHEN price > 50 THEN 10
ELSE 5
END
WHERE category = 'Clothing';
Summary
Key Takeaways
- UPDATE modifies existing records in a table
- Always include a WHERE clause unless updating all rows intentionally
- Preview with SELECT before running UPDATE
- You can update multiple columns and use expressions
- Use transactions for safety on critical updates
- Batch large updates to avoid long locks and log bloat
| Operation | Speed | Logging | Reversible |
|---|---|---|---|
| UPDATE | Moderate | Full | Only in transaction |
| DELETE | Moderate | Full | Only in transaction |
| INSERT | Fast | Minimal | No |