UPDATE with RETURNING clause in PostgreSQL - Mini Project: Build & Apply

Update Rows with RETURNING Clause in PostgreSQL

📖 Scenario: You manage a small bookstore database. You want to update the prices of some books and see which books were changed immediately.

🎯 Goal: Build a SQL query that updates book prices and returns the updated rows using the RETURNING clause.

📋 What You'll Learn

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

Insert 3 specific books with given prices

Write an UPDATE statement to increase prices by 10% for books priced below 20

Use the RETURNING clause to get the updated rows

💡 Why This Matters

🌍 Real World

Updating product prices or user information in a database and immediately seeing which records changed is common in business applications.

💼 Career

Knowing how to update data and retrieve the changed rows efficiently is a valuable skill for database administrators and backend developers.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), and price (numeric). Then insert these three rows exactly: (1, 'The Hobbit', 15.00), (2, '1984', 22.50), and (3, 'Dune', 18.00).

PostgreSQL

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

Need a hint?

Use CREATE TABLE to define the structure. Use INSERT INTO with multiple rows to add data.

2

Set the price threshold for update

Create a variable or placeholder in SQL called price_limit and set it to 20. This will be used to select books priced below this value.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Hobbit', 15.00),
  (2, '1984', 22.50),
  (3, 'Dune', 18.00);

-- Define price_limit variable
-- Your code here

Need a hint?

In psql, use \set price_limit 20 to create a variable.

3

Write the UPDATE query to increase prices

Write an UPDATE statement on the books table that increases the price by 10% for all books where price is less than the variable :price_limit. Use SET price = price * 1.10.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Hobbit', 15.00),
  (2, '1984', 22.50),
  (3, 'Dune', 18.00);

\set price_limit 20

-- Update prices by 10% for books priced below :price_limit
-- Your code here

Need a hint?

Use UPDATE books SET price = price * 1.10 WHERE price < :price_limit.

4

Add the RETURNING clause to see updated rows

Add a RETURNING clause to the previous UPDATE statement to return the id, title, and updated price of the books that were changed.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Hobbit', 15.00),
  (2, '1984', 22.50),
  (3, 'Dune', 18.00);

\set price_limit 20

UPDATE books
SET price = price * 1.10
WHERE price < :price_limit
-- Add RETURNING clause here
-- Your code here

Need a hint?

Add RETURNING id, title, price after the WHERE clause.