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

Need a 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

Need a 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

Need a 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

Need a hint?

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