UPDATE multiple columns in SQL - Mini Project: Build & Apply

Update Multiple Columns in a Database Table

📖 Scenario: You manage a small bookstore database. You want to update the prices and stock quantities of some books after a new shipment arrives.

🎯 Goal: Learn how to update multiple columns in a SQL table using a single UPDATE statement.

📋 What You'll Learn

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

Insert three books with specific values into the books table.

Write an UPDATE statement to change the price and stock of a specific book.

Use a WHERE clause to update only the intended book.

💡 Why This Matters

🌍 Real World

Updating multiple columns in a database is common when managing inventories, user profiles, or any data that changes over time.

💼 Career

Database administrators and developers often write UPDATE queries to maintain accurate and current data in business applications.

1

Create the books table

Write a SQL statement to create a table called books with these columns: id as an integer primary key, title as text, price as a decimal number, and stock as an integer.

SQL

-- Create the books table with columns id, title, price, and stock
-- Your code here

Need a hint?

Use CREATE TABLE books and define each column with its type. Remember to set id as the primary key.

2

Insert initial book records

Insert these three books into the books table with exact values: (1, 'The Great Gatsby', 10.99, 5), (2, '1984', 8.99, 8), and (3, 'To Kill a Mockingbird', 12.50, 3).

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price DECIMAL(5,2),
  stock INTEGER
);
-- Insert the three books below
-- Your code here

Need a hint?

Use a single INSERT INTO books statement with multiple rows separated by commas.

3

Write the UPDATE statement to change price and stock

Write an UPDATE statement to change the price to 9.99 and stock to 10 for the book with id = 2.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price DECIMAL(5,2),
  stock INTEGER
);

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

-- Update price and stock for book with id 2
-- Your code here

Need a hint?

Use UPDATE books SET price = 9.99, stock = 10 WHERE id = 2; to update both columns at once.

4

Complete the update with a confirmation query

Add a SELECT statement to show all columns for the book with id = 2 to confirm the update.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price DECIMAL(5,2),
  stock INTEGER
);

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

UPDATE books
SET price = 9.99, stock = 10
WHERE id = 2;

-- Select the updated book to confirm changes
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE id = 2; to see the updated row.