Modifying Records with UPDATE0%
Removing Records with DELETE vs TRUNCATE

Modifying Records with UPDATE

Beginner12 min read•Updated: 2026-09-12
Study Materials

Modifying Records with UPDATE: Precision Filtering & Safe Updates

Data in a production database is never static. Customer addresses change, account balances fluctuate after purchases, and order statuses transition from Processing to Shipped. The UPDATE statement modifies existing records in a table without altering the table structure.

1. Basic UPDATE Syntax

SQL
UPDATE table_name
SET column_1 = new_value_1,
column_2 = new_value_2,
...
WHERE filtering_condition;

Real-World Example:

SQL
-- Update phone number and status for customer ID 105:
UPDATE customers
SET phone_number = '+91-9876543210',
account_status = 'Active'
WHERE customer_id = 105;
Important Warning
[!CAUTION] The Classic Disaster: If you execute UPDATE customers SET account_status = 'Active'; without a WHERE clause, every single customer row in your database will be modified! Always double-check your WHERE clause before running UPDATE.

2. MySQL Safe Updates Mode (sql_safe_updates)

Because accidentally running an UPDATE without a WHERE clause can destroy millions of dollars of company data, MySQL features Safe Updates Mode:

SQL
-- Check current Safe Updates status:
SHOW VARIABLES LIKE 'sql_safe_updates';
 
-- In Safe Updates Mode (1 = ON):
-- MySQL will REFUSE to execute any UPDATE or DELETE statement that:
-- 1. Does not contain a WHERE clause, OR
-- 2. Uses a WHERE clause that does NOT filter by a KEY column (Primary/Unique index),
-- unless a LIMIT clause is provided!

Safe Updates Error:

Output
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

Temporarily Disabling Safe Updates for Migration Scripts:

SQL
SET SQL_SAFE_UPDATES = 0; -- Disable safe updates for active session
UPDATE products SET discount = 0.10 WHERE category = 'Electronics';
SET SQL_SAFE_UPDATES = 1; -- Re-enable immediately!

3. Mathematical & Relative Updates

You can update columns based on their current stored values:

SQL
-- Give all employees in department 3 an 8% salary raise:
UPDATE employees
SET salary = salary * 1.08
WHERE department_id = 3;
 
-- Increment view count on a blog article:
UPDATE blog_posts
SET views_count = views_count + 1
WHERE post_id = 42;

4. Conditional Updates with CASE

You can update different rows with different values in a single statement using conditional CASE:

SQL
UPDATE employees
SET salary = CASE
WHEN performance_rating = 'Outstanding' THEN salary * 1.15
WHEN performance_rating = 'Exceeds' THEN salary * 1.10
WHEN performance_rating = 'Meets' THEN salary * 1.05
ELSE salary
END
WHERE is_active = TRUE;

5. Limiting Update Scope (ORDER BY & LIMIT)

MySQL uniquely supports ordering and limiting updates:

SQL
-- Mark only the 10 oldest pending orders as priority:
UPDATE orders
SET is_priority = TRUE
WHERE order_status = 'Pending'
ORDER BY created_at ASC
LIMIT 10;

6. Best Practices & Common Pitfalls

  • Test with SELECT First: Before executing an UPDATE ... WHERE <condition>, convert the query into SELECT * FROM table WHERE <condition> to verify exactly which rows will be affected!
  • Use Transactions: In production databases, wrap sensitive updates inside a transaction:
SQL
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Inspect affected rows. If correct:
COMMIT;
-- If wrong:
ROLLBACK;

Multiple Choice Questions

1. What catastrophic consequence occurs if you execute an UPDATE statement without a WHERE clause?

A. The statement fails with a syntax error B. Every row in the entire table is updated with the new values C. Only the first row is updated D. The table is converted to read-only Answer: B Explanation: Without a WHERE clause to filter rows, the UPDATE statement applies the new values across every single record in the table.

2. What is the primary purpose of MySQL's SQL_SAFE_UPDATES configuration?

A. To encrypt database backups B. To prevent accidental mass updates or deletions by blocking queries that lack a WHERE clause utilizing an indexed key C. To force all users to change passwords every 30 days D. To disable network connections Answer: B Explanation: SQL_SAFE_UPDATES blocks UPDATE and DELETE queries that do not filter by a primary or unique key column, preventing catastrophic unintended bulk modifications.

3. Which query gives a 10% salary increase to all employees in department 2?

A. UPDATE employees SET salary = salary 1.10 WHERE department_id = 2; B. MODIFY employees salary = salary + 10 WHERE department_id = 2; C. ALTER TABLE employees SET salary = salary 1.10; D. CHANGE employees SET salary = 1.10 WHERE department_id = 2; Answer: A Explanation: The UPDATE ... SET salary = salary * 1.10 WHERE department_id = 2; statement applies mathematical relative updates filtered to department 2.

4. Which MySQL-specific clauses can be used with an UPDATE statement to restrict modifications to the top 5 oldest records?

A. TOP 5 ONLY B. ORDER BY created_at ASC LIMIT 5 C. FETCH FIRST 5 ROWS ONLY D. MAX ROWS = 5 Answer: B Explanation: MySQL allows pairing ORDER BY with LIMIT in UPDATE statements to restrict changes to a specific ordered subset of rows.

5. Why should developers run a SELECT query with the intended WHERE clause prior to executing an UPDATE?

A. To warm up the CPU cache B. To visually inspect and verify the exact subset of rows that will be modified before making changes C. Because MySQL requires a SELECT before an UPDATE D. To lock the table against other users Answer: B Explanation: Testing the filter condition with SELECT ensures you verify the exact target rows beforehand, preventing unintended data modifications.

Next Lesson

Removing Records with DELETE vs TRUNCATE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
INSERT IGNORE & ON DUPLICATE KEY UPDATERemoving Records with DELETE vs TRUNCATE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext