SQL Fundamentals

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?

UPDATE employees
SET salary = 85000
WHERE id = 1;

Basic Syntax

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;
```sql
-- 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.

-- Give all Engineering employees a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';
```sql
-- 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;
```sql
-- 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';

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.

-- 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

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 &lt; 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

Operation	Speed	Logging	Reversible
UPDATE	Moderate	Full	Only in transaction
DELETE	Moderate	Full	Only in transaction
INSERT	Fast	Minimal	No

UPDATE Statement

UPDATE Statement

What Is UPDATE?

Basic Syntax

Update with Expressions

Safe Update Pattern

Batch Updates

Common Pitfalls

Performance Considerations

Practice Exercises

Summary

Need Expert SQL Help?