SQL UPDATE Statement | Modify Existing Data Safely

# CHAPTER 21

UPDATE Queries and Modifying Data #

1. Introduction #

2. Learning Objectives #

• Understand the syntax of the UPDATE and SET clauses.

• Modify a specific, single row using a Primary Key filter.

• Update multiple columns simultaneously.

• Perform bulk mathematical updates across the database.

• Recognize and avoid the catastrophic "Unfiltered Update" mistake.

3. The UPDATE Syntax #

1. 1. UPDATE: Specify the table you want to modify.

1. 2. SET: Specify the column(s) you want to change, and their new values.

1. 3. WHERE: Specify EXACTLY which row(s) should be changed. *(If you forget this, you destroy the table).*

-- Safely update John's email address
UPDATE users 
SET email = 'john.new@gmail.com' 
WHERE id = 42;

4. Updating Multiple Columns #

UPDATE employees 
SET 
    last_name = 'Smith-Jones',
    city = 'Seattle'
WHERE employee_id = 105;

5. Bulk Mathematical Updates #

UPDATE employees 
-- SQL mathematically adds 5000 to whatever the current salary is!
SET salary = salary + 5000 
WHERE department = 'Sales';

*(Result: If there are 500 people in Sales, all 500 salaries are instantly updated!)*

6. The Catastrophe: The Unfiltered Update #

-- DANGER! NEVER DO THIS!
UPDATE employees 
SET salary = 100000;

DISASTER. Because there is no WHERE clause to filter the command, SQL assumes you want to apply the change to the *entire table*. The CEO, the janitor, and the interns all instantly have their salaries set to exactly $100,000. Unless you have a backup, your career is over.

7. Mini Project: E-Commerce Inventory Management #

-- 1. A customer buys 1 unit of Product #88. Reduce the stock!
UPDATE inventory 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 88;

-- 2. The Marketing team wants a 20% off Black Friday sale on all Laptops!
UPDATE products 
SET price = price * 0.80 
WHERE category = 'Laptop';

8. Safe Update Mode #

9. Common Mistakes #

• Putting Commas before the WHERE clause:

10. Best Practices #

• The SELECT Verification Trick: Professional DBAs never write an UPDATE statement blindly. First, they write a SELECT statement with their WHERE clause: SELECT * FROM users WHERE status = 'Pending'. They check the grid to ensure they are targeting the correct 5 rows. Only then do they change the word SELECT * FROM to UPDATE.

11. Exercises #

1. 1. What two SQL keywords are required to specify which table and which columns are being modified?

1. 2. What happens if you execute an UPDATE command without a WHERE clause?

12. SQL Challenges #

UPDATE users 
SET status = 'Inactive' 
WHERE last_login_date < &#039;2022-01-01';

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the "SELECT Verification Trick" workflow. Why is this considered an essential best practice for Database Administrators before executing UPDATE or DELETE commands on a production server?

• Q: Write the SQL syntax to increase the price of all products in the Electronics category by 15%, updating multiple columns if necessary.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #