UPDATE

---

title: “UPDATE” part: 1 topic_number: 3 slug: “update” difficulty: “Beginner” prerequisites: “insert” —

UPDATE

What Is It?

UPDATE modifies existing data in a table. You use it to change values in one or more rows. Think of it like editing a spreadsheet cell — the row stays, but the value changes.

Real-world analogy: An employee gets a promotion and a raise. You don’t delete them and re-add them — you UPDATE their salary and department.

---

Syntax Breakdown

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;

Breaking it down:

• UPDATE — The command that says “I’m modifying data”
• table_name — Which table you’re modifying
• SET — Keyword followed by the columns you want to change
• column1 = new_value1 — Which column to change and what the new value should be
• WHERE — CRITICAL: Specifies which rows to update (without it, ALL rows get updated!)

The WHERE clause is your safety net. Always use it unless you genuinely want to update every single row.

---

Basic Example

Let’s give employee Paul Garcia (employee_id = 16) a raise and assign him to a department.

UPDATE employees
SET salary = 68000.00, department_id = 6
WHERE employee_id = 16;

What this does:

• Finds the employee with ID 16 (Paul)
• Changes his salary from $60,000 to $68,000
• Assigns him to department 6 (Customer Support)

Expected output:

Query OK, 1 row affected

Verify it worked:

SELECT first_name, last_name, salary, department_id 
FROM employees 
WHERE employee_id = 16;

first_name	last_name	salary	department_id
Paul	Garcia	68000.00	6

---

Going Deeper

Updating Multiple Rows

You can update many rows at once if they match your WHERE condition:

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 2;

What this does:

• Finds all employees in department 2 (Marketing)
• Increases their salary by 5% (multiplies current salary by 1.05)
• Updates Carol, David, and Rachel

Pause and Predict: If Carol’s salary was $78,000, what will it be after this update?

Answer

$81,900 (78,000 × 1.05 = 81,900)

Expected output:

Query OK, 3 rows affected

Updating Based on Another Column

You can use a column’s current value to calculate the new value:

UPDATE projects
SET end_date = DATE_ADD(start_date, INTERVAL 6 MONTH)
WHERE end_date IS NULL;

What this does:

• Finds all projects with no end date (NULL)
• Sets the end date to 6 months after the start date
• Useful for setting estimated completion dates

---

Watch Out — Common Mistakes

Mistake #1: Forgetting the WHERE Clause (DANGEROUS!)

--  WRONG — THIS UPDATES EVERY EMPLOYEE!
UPDATE employees
SET salary = 50000.00;

Why it’s catastrophic: Without a WHERE clause, this sets EVERY employee’s salary to $50,000. Alice the CEO? $50,000. Everyone? $50,000. This is almost never what you want.

Expected output:

Query OK, 20 rows affected  -- Uh oh, that's everyone!

-- • CORRECT
UPDATE employees
SET salary = 50000.00
WHERE employee_id = 16;  -- Only updates Paul

Best practice: Always write your WHERE clause first, test it with a SELECT, then write the UPDATE.

Mistake #2: Wrong WHERE Condition

--  WRONG
UPDATE employees
SET salary = 100000.00
WHERE first_name = 'Bob Smith';  -- Comparing first_name to full name!

Why it fails: The column first_name contains “Bob”, not “Bob Smith”. This WHERE condition matches zero rows, so nothing gets updated.

Expected output:

Query OK, 0 rows affected

Notice it didn’t error — it just didn’t find any matching rows. This is silent failure!

-- • CORRECT
UPDATE employees
SET salary = 100000.00
WHERE first_name = 'Bob' AND last_name = 'Smith';

Mistake #3: Data Type Mismatch

--  WRONG
UPDATE employees
SET hire_date = 2023-05-01;  -- Missing quotes! MySQL treats this as math: 2023 minus 5 minus 1

Why it fails: Without quotes, MySQL interprets 2023-05-01 as subtraction: 2023 - 5 - 1 = 2017. You’d be setting hire_date to the number 2017, which isn’t a valid date format.

-- • CORRECT
UPDATE employees
SET hire_date = '2023-05-01';  -- Dates need quotes

---

Edge Case Spotlight

Updating to NULL

You can explicitly set a column to NULL (if the column allows it):

UPDATE employees
SET manager_id = NULL
WHERE employee_id = 1;

This removes Alice’s manager (she’s the CEO, so she reports to no one).

But be careful: If a column is defined as NOT NULL, trying to set it to NULL will cause an error:

UPDATE employees
SET first_name = NULL
WHERE employee_id = 1;

Error: Column 'first_name' cannot be null

Lesson: You can only set a column to NULL if the table definition allows it. Check your CREATE TABLE statement to know which columns accept NULL.

---

Try This

Exercise 1 (Guided)

Employee Noah Harris (employee_id = 14) finally got his salary approved. Set it to $72,000.

Hint

You're updating the `employees` table. Which column needs to change? What's the WHERE condition to target only Noah?

Exercise 2 (Independent)

All projects with a budget under $100,000 just got additional funding. Increase their budgets by 20%.

Hint

Think about: (1) Which table? (2) What's the SET clause with a calculation? (3) What's the WHERE condition to filter by budget amount?

---

Answer Key

Exercise 1 Answer

```sql UPDATE employees SET salary = 72000.00 WHERE employee_id = 14; ``` Verify: ```sql SELECT first_name, last_name, salary FROM employees WHERE employee_id = 14; ``` Expected: Noah Harris now has a salary of $72,000.

Exercise 2 Answer

```sql UPDATE projects SET budget = budget * 1.20 WHERE budget < 100000.00; ``` **What this updates:** - Marketing Campaign Q1 ($80,000 → $96,000) - Security Audit ($75,000 → $90,000) - Product Rebranding (wait, this is $120,000, so it's NOT updated) Actually, looking at the data, only 2 projects have budgets under $100,000. The WHERE clause ensures we only update those. **Edge case:** What about projects where budget IS NULL? They won't match `budget < 100000.00`, so they stay NULL. NULL comparisons always evaluate to false (we'll cover this more in the NULL handling topic).

---

Quick Recap

• UPDATE modifies existing rows in a table
• Always use WHERE unless you really want to update every row
• Test your WHERE clause with SELECT first
• You can update multiple columns in one UPDATE statement
• You can use a column’s current value in calculations

---

Up Next

Next topic: DELETE → part1_04_delete.md

You can add data (INSERT) and change data (UPDATE). Next, you’ll learn how to remove data — carefully!