Modifying Records with UPDATE0%

Removing Records with DELETE vs TRUNCATE

Modifying Records with UPDATE

Beginner12 min read•Updated: 2026-09-12

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

1UPDATE table_name
2SET column_1 = new_value_1,
3    column_2 = new_value_2,
4    ...
5WHERE filtering_condition;

Real-World Example:

1-- Update phone number and status for customer ID 105:
2UPDATE customers
3SET phone_number = '+91-9876543210',
4    account_status = 'Active'
5WHERE 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:

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

Safe Updates Error:

1Error 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:

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

3. Mathematical & Relative Updates

You can update columns based on their current stored values:

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

4. Conditional Updates with `CASE`

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

1UPDATE employees
2SET salary = CASE 
3    WHEN performance_rating = 'Outstanding' THEN salary * 1.15
4    WHEN performance_rating = 'Exceeds'     THEN salary * 1.10
5    WHEN performance_rating = 'Meets'       THEN salary * 1.05
6    ELSE salary
7END
8WHERE is_active = TRUE;

5. Limiting Update Scope (`ORDER BY` & `LIMIT`)

MySQL uniquely supports ordering and limiting updates:

1-- Mark only the 10 oldest pending orders as priority:
2UPDATE orders
3SET is_priority = TRUE
4WHERE order_status = 'Pending'
5ORDER BY created_at ASC
6LIMIT 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:

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Inspect affected rows. If correct:
COMMIT;
-- If wrong:
ROLLBACK;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Removing Records with DELETE vs TRUNCATE

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

Related Lessons

Previous Lesson	Next Lesson
INSERT IGNORE & ON DUPLICATE KEY UPDATE	Removing Records with DELETE vs TRUNCATE

Practice Quiz

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

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Removing Records with DELETE vs TRUNCATE

Modifying Records with UPDATE

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Modifying Records with UPDATE: Precision Filtering & Safe Updates

1. Basic `UPDATE` Syntax

1UPDATE table_name
2SET column_1 = new_value_1,
3    column_2 = new_value_2,
4    ...
5WHERE filtering_condition;

Real-World Example:

1-- Update phone number and status for customer ID 105:
2UPDATE customers
3SET phone_number = '+91-9876543210',
4    account_status = 'Active'
5WHERE customer_id = 105;

Important Warning

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:

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

Safe Updates Error:

1Error 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:

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

3. Mathematical & Relative Updates

You can update columns based on their current stored values:

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

4. Conditional Updates with `CASE`

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

1UPDATE employees
2SET salary = CASE 
3    WHEN performance_rating = 'Outstanding' THEN salary * 1.15
4    WHEN performance_rating = 'Exceeds'     THEN salary * 1.10
5    WHEN performance_rating = 'Meets'       THEN salary * 1.05
6    ELSE salary
7END
8WHERE is_active = TRUE;

5. Limiting Update Scope (`ORDER BY` & `LIMIT`)

MySQL uniquely supports ordering and limiting updates:

1-- Mark only the 10 oldest pending orders as priority:
2UPDATE orders
3SET is_priority = TRUE
4WHERE order_status = 'Pending'
5ORDER BY created_at ASC
6LIMIT 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:

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Inspect affected rows. If correct:
COMMIT;
-- If wrong:
ROLLBACK;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Removing Records with DELETE vs TRUNCATE

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

Related Lessons

Previous Lesson	Next Lesson
INSERT IGNORE & ON DUPLICATE KEY UPDATE	Removing Records with DELETE vs TRUNCATE

Practice Quiz

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

Previous Next

Modifying Records with UPDATE: Precision Filtering & Safe Updates

1. Basic UPDATE Syntax

Real-World Example:

2. MySQL Safe Updates Mode (sql_safe_updates)

Safe Updates Error:

Temporarily Disabling Safe Updates for Migration Scripts:

3. Mathematical & Relative Updates

4. Conditional Updates with CASE

5. Limiting Update Scope (ORDER BY & LIMIT)

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Removing Records with DELETE vs TRUNCATE

Related Lessons

Practice Quiz

Loading Page Content...

Modifying Records with UPDATE: Precision Filtering & Safe Updates

1. Basic UPDATE Syntax

Real-World Example:

2. MySQL Safe Updates Mode (sql_safe_updates)

Safe Updates Error:

Temporarily Disabling Safe Updates for Migration Scripts:

3. Mathematical & Relative Updates

4. Conditional Updates with CASE

5. Limiting Update Scope (ORDER BY & LIMIT)

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Removing Records with DELETE vs TRUNCATE

Related Lessons

Practice Quiz

1. Basic `UPDATE` Syntax

2. MySQL Safe Updates Mode (`sql_safe_updates`)

4. Conditional Updates with `CASE`

5. Limiting Update Scope (`ORDER BY` & `LIMIT`)

1. Basic `UPDATE` Syntax

2. MySQL Safe Updates Mode (`sql_safe_updates`)

4. Conditional Updates with `CASE`

5. Limiting Update Scope (`ORDER BY` & `LIMIT`)