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

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

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

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

Hint

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

Practice

1. What does the RETURNING clause do in a PostgreSQL UPDATE statement?

easy

A. It returns the updated rows immediately after the update.

B. It deletes the rows after updating them.

C. It creates a backup of the updated rows.

D. It prevents the update from happening.

Solution

1. Step 1: Understand the purpose of UPDATE

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

2. Step 2: Role of RETURNING clause

   The RETURNING clause immediately outputs the rows that were updated, showing their new values.

3. Final Answer:

   It returns the updated rows immediately after the update. -> Option A

4. Quick Check:

   RETURNING shows updated rows [OK]

Hint: RETURNING shows updated rows right after update [OK]

Common Mistakes:

• Thinking RETURNING deletes rows
• Assuming RETURNING creates backups
• Believing RETURNING stops the update

2. Which of the following is the correct syntax to update a table and return the updated rows in PostgreSQL?

easy

A. UPDATE table_name SET column1 = value1 WHERE condition RETURNING *;

B. UPDATE table_name SET column1 = value1 RETURN * WHERE condition;

C. UPDATE table_name RETURNING * SET column1 = value1 WHERE condition;

D. UPDATE table_name SET column1 = value1 WHERE condition SHOW RETURNING *;

Solution

1. Step 1: Identify correct UPDATE syntax

   The UPDATE statement must have SET clause before WHERE clause.

2. Step 2: Position of RETURNING clause

   RETURNING clause comes after WHERE clause to output updated rows.

3. Final Answer:

   UPDATE table_name SET column1 = value1 WHERE condition RETURNING *; -> Option A

4. Quick Check:

   RETURNING follows WHERE in UPDATE [OK]

Hint: RETURNING always comes after WHERE clause [OK]

Common Mistakes:

• Placing RETURNING before SET clause
• Using SHOW RETURNING which is invalid
• Putting RETURNING between SET and WHERE

3. Given the table employees with columns id, salary, and department, what will this query return?

UPDATE employees SET salary = salary + 500 WHERE department = 'Sales' RETURNING id, salary;

medium

A. Only the number of rows updated, no data returned.

B. All employees' ids and salaries regardless of department.

C. All employees' ids and new salaries in the Sales department after the raise.

D. An error because RETURNING cannot specify columns.

Solution

1. Step 1: Understand the UPDATE condition

   The query updates salary by adding 500 only for employees in the Sales department.

2. Step 2: Effect of RETURNING clause

   RETURNING id, salary outputs the id and updated salary of only those employees updated.

3. Final Answer:

   All employees' ids and new salaries in the Sales department after the raise. -> Option C

4. Quick Check:

   RETURNING outputs updated rows only [OK]

Hint: RETURNING shows only updated rows, not all rows [OK]

Common Mistakes:

• Thinking all rows are returned
• Expecting only count of updated rows
• Believing RETURNING cannot specify columns

4. What is wrong with this query?

UPDATE products RETURNING * SET price = price * 1.1 WHERE category = 'Books';

medium

A. The price calculation is invalid syntax.

B. The RETURNING clause is placed before SET, causing a syntax error.

C. The WHERE clause is missing.

D. RETURNING cannot be used with UPDATE.

Solution

1. Step 1: Check clause order in UPDATE

   UPDATE syntax requires SET before WHERE and RETURNING last.

2. Step 2: Identify error in query

   RETURNING appears before SET, which is invalid and causes syntax error.

3. Final Answer:

   The RETURNING clause is placed before SET, causing a syntax error. -> Option B

4. Quick Check:

   RETURNING must come after WHERE and SET [OK]

Hint: RETURNING always comes last after WHERE [OK]

Common Mistakes:

• Placing RETURNING before SET
• Omitting WHERE clause when needed
• Misordering clauses in UPDATE

5. You want to increase the salary by 10% for employees in the 'Engineering' department and immediately get their id, old_salary, and new_salary. Which query correctly does this using UPDATE with RETURNING?

hard

A. UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering' RETURNING id, salary, salary / 1.1 AS old_salary;

B. UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering' RETURNING id, salary, salary * 1.1 AS old_salary;

C. UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering' RETURNING id, salary AS old_salary, salary * 1.1 AS new_salary;

D. UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering' RETURNING id, salary AS new_salary, salary / 1.1 AS old_salary;

Solution

1. Step 1: Understand the salary update

   Salary is multiplied by 1.1 to increase by 10%, so new salary is salary * 1.1.

2. Step 2: Calculate old salary in RETURNING

   After update, salary column shows new salary. To get old salary, divide new salary by 1.1.

3. Step 3: Check aliasing in RETURNING

   RETURNING must show id, new_salary (salary), and old_salary (salary / 1.1) with correct aliases.

4. Final Answer:

   UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering' RETURNING id, salary AS new_salary, salary / 1.1 AS old_salary; -> Option D

5. Quick Check:

   Old salary = new salary / 1.1 [OK]

Hint: Use RETURNING with expressions and aliases for old and new values [OK]

Common Mistakes:

• Confusing old and new salary calculations
• Wrong aliasing in RETURNING clause
• Using expressions incorrectly in RETURNING