UPDATE with subquery preview in SQL - Mini Project: Build & Apply

UPDATE with subquery preview

📖 Scenario: You manage a small bookstore database. You want to update the prices of some books based on their current prices.

🎯 Goal: Build an UPDATE SQL query that uses a subquery to preview the new prices before applying the update.

📋 What You'll Learn

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

Insert 3 books with exact titles and prices.

Create a subquery that selects the id and the new price (current price + 5).

Write an UPDATE statement that uses the subquery to update the prices.

💡 Why This Matters

🌍 Real World

Updating prices in a product database based on calculated values is common in retail and inventory management.

💼 Career

Knowing how to use UPDATE with subqueries helps database administrators and developers maintain accurate and dynamic data.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and price (integer). Then insert these exact rows: (1, 'Book A', 10), (2, 'Book B', 15), (3, 'Book C', 20).

SQL

-- Create table books and insert 3 rows
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Write a subquery to preview new prices

Create a subquery called price_preview that selects id and calculates a new price as price + 5 from the books table.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10),
(2, 'Book B', 15),
(3, 'Book C', 20);

-- Create subquery price_preview
-- Your code here

Need a hint?

Use a WITH clause to create the subquery named price_preview.

3

Write the UPDATE statement using the subquery

Write an UPDATE statement that updates the price in books by joining with the price_preview subquery on id, setting price to new_price.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10),
(2, 'Book B', 15),
(3, 'Book C', 20);

WITH price_preview AS (
  SELECT id, price + 5 AS new_price FROM books
)

-- Write UPDATE statement using price_preview
-- Your code here

Need a hint?

Use UPDATE with FROM to join the subquery and set the new prices.

4

Complete the query with a preview SELECT

Add a SELECT statement after the UPDATE to preview the updated id, title, and price from the books table.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10),
(2, 'Book B', 15),
(3, 'Book C', 20);

WITH price_preview AS (
  SELECT id, price + 5 AS new_price FROM books
)
UPDATE books
SET price = price_preview.new_price
FROM price_preview
WHERE books.id = price_preview.id;

-- Add SELECT to preview updated books
-- Your code here

Need a hint?

Use SELECT to show the updated table content.