UPDATE with WHERE clause in MySQL - Mini Project: Build & Apply

Update Records with WHERE Clause in MySQL

📖 Scenario: You are managing a small bookstore database. You want to update the price of a specific book based on its title.

🎯 Goal: Learn how to update a record in a MySQL table using the UPDATE statement with a WHERE clause to target specific rows.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert three specific book records into the books table

Write an UPDATE statement to change the price of one book using a WHERE clause

Verify the update affects only the targeted book

💡 Why This Matters

🌍 Real World

Updating specific records in a database is common when prices, statuses, or other details change for certain items.

💼 Career

Database administrators and developers often need to update records safely without affecting unrelated data.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as a variable character string of length 100, and price as a decimal number with 5 digits total and 2 decimal places.

MySQL

-- Your code here to create the books table

Hint

Use CREATE TABLE with column definitions including PRIMARY KEY.

2

Insert three book records

Write three INSERT INTO books statements to add these exact books: (1, 'The Great Gatsby', 10.99), (2, '1984', 8.99), and (3, 'To Kill a Mockingbird', 12.50).

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);
-- Your code here to insert three books

Hint

Use separate INSERT INTO statements for each book with exact values.

3

Write an UPDATE statement with WHERE clause

Write an UPDATE books statement to change the price to 9.99 only for the book with the title '1984'. Use a WHERE clause to target this book.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES (1, 'The Great Gatsby', 10.99);
INSERT INTO books (id, title, price) VALUES (2, '1984', 8.99);
INSERT INTO books (id, title, price) VALUES (3, 'To Kill a Mockingbird', 12.50);
-- Your code here to update the price of '1984' to 9.99

Hint

Use UPDATE with SET and a WHERE clause to change only the targeted book's price.

4

Verify the update with a SELECT statement

Write a SELECT * FROM books WHERE title = '1984'; statement to check that only the price of '1984' has changed to 9.99.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES (1, 'The Great Gatsby', 10.99);
INSERT INTO books (id, title, price) VALUES (2, '1984', 8.99);
INSERT INTO books (id, title, price) VALUES (3, 'To Kill a Mockingbird', 12.50);

UPDATE books SET price = 9.99 WHERE title = '1984';
-- Your code here to select the updated book

Hint

Use SELECT * FROM books WHERE title = '1984'; to see the updated record.

Practice

1. What does the WHERE clause do in an UPDATE statement in MySQL?

easy

A. It specifies which rows should be updated.

B. It creates a new table.

C. It deletes rows from the table.

D. It selects rows to display.

Solution

1. Step 1: Understand the purpose of UPDATE

   The UPDATE command changes data in existing rows of a table.

2. Step 2: Role of WHERE clause

   The WHERE clause limits which rows get changed. Without it, all rows update.

3. Final Answer:

   It specifies which rows should be updated. -> Option A

4. Quick Check:

   WHERE limits rows to update [OK]

Hint: WHERE filters rows to update; no WHERE updates all [OK]

Common Mistakes:

• Thinking WHERE creates or deletes rows
• Confusing UPDATE with SELECT
• Ignoring WHERE causes all rows to update

2. Which of the following is the correct syntax to update the salary to 5000 for employees with id 10 in MySQL?

easy

A. UPDATE employees SET salary = 5000 AND id = 10;

B. UPDATE employees SET salary = 5000 WHERE id = 10;

C. UPDATE employees SET salary WHERE id = 10 = 5000;

D. UPDATE employees WHERE id = 10 SET salary = 5000;

Solution

1. Step 1: Identify correct UPDATE syntax

   The correct order is: UPDATE table SET column = value WHERE condition.

2. Step 2: Check each option

   UPDATE employees SET salary = 5000 WHERE id = 10; follows correct syntax. Others have misplaced clauses or wrong operators.

3. Final Answer:

   UPDATE employees SET salary = 5000 WHERE id = 10; -> Option B

4. Quick Check:

   UPDATE SET column=value WHERE condition [OK]

Hint: SET comes before WHERE in UPDATE statements [OK]

Common Mistakes:

• Placing WHERE before SET
• Using AND instead of WHERE
• Incorrect assignment syntax

3. Given the table products with columns id, name, and price, what will be the price of product with id = 3 after running this query?

UPDATE products SET price = price + 10 WHERE id = 3;

Assume the original price for id = 3 was 50.

medium

A. 10

B. 50

C. 60

D. NULL

Solution

1. Step 1: Understand the UPDATE operation

   The query adds 10 to the current price for the product where id = 3.

2. Step 2: Calculate new price

   Original price is 50, so 50 + 10 = 60.

3. Final Answer:

   60 -> Option C

4. Quick Check:

   50 + 10 = 60 [OK]

Hint: Add to existing value only for rows matching WHERE [OK]

Common Mistakes:

• Ignoring WHERE and updating all rows
• Setting price to 10 instead of adding
• Confusing original and updated values

4. Identify the error in this MySQL query:

UPDATE users SET age = 30 WHERE;

medium

A. SET keyword is misplaced.

B. Table name is missing.

C. UPDATE keyword is misspelled.

D. Missing condition after WHERE clause.

Solution

1. Step 1: Analyze the query structure

   The query has UPDATE, SET, and WHERE keywords but WHERE has no condition.

2. Step 2: Identify the error

   WHERE must be followed by a condition to specify which rows to update.

3. Final Answer:

   Missing condition after WHERE clause. -> Option D

4. Quick Check:

   WHERE requires condition [OK]

Hint: WHERE must always have a condition [OK]

Common Mistakes:

• Leaving WHERE empty
• Misplacing SET and WHERE
• Forgetting semicolon

5. You have a table orders with columns order_id, status, and amount. You want to increase the amount by 20% only for orders with status 'pending' and amount less than 100. Which query correctly does this?

hard

A. UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' AND amount < 100;

B. UPDATE orders SET amount = amount + 20 WHERE status = 'pending' OR amount < 100;

C. UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' OR amount < 100;

D. UPDATE orders SET amount = amount * 0.2 WHERE status = 'pending' AND amount < 100;

Solution

1. Step 1: Understand the condition

   We want to update only rows where status is 'pending' AND amount is less than 100.

2. Step 2: Calculate new amount

   Increase by 20% means multiply amount by 1.2.

3. Step 3: Check options

   UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' AND amount < 100; correctly uses AND and multiplies amount by 1.2. Others use OR or wrong calculations.

4. Final Answer:

   UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' AND amount < 100; -> Option A

5. Quick Check:

   AND condition + *1.2 [OK]

Hint: Use AND for multiple conditions in WHERE [OK]

Common Mistakes:

• Using OR instead of AND
• Adding fixed 20 instead of 20%
• Multiplying by 0.2 instead of 1.2