Delete and Return
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 18 of 22.
DELETE FROM removes rows that match its WHERE condition. RETURNING can show values from the removed rows so you can inspect what was deleted. Without WHERE, DELETE targets every row.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
DELETE FROM products WHERE id = 5 RETURNING id, name;The result identifies lamp even though that row has been removed. DELETE removes rows, while DROP TABLE removes a whole table object; they solve different problems.
Use DELETE with a precise WHERE condition. RETURNING reports the removed rows.
Challenge
MediumRemove product id 3 from this exercise database and return id and name for the removed row.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
1Types and Casts
Convert with CastsExact Decimal DivisionBoolean ValuesCreate a Typed TableActive Price Review4Change Data Safely
Insert and ReturnUpdate Selected RowsDelete and ReturnTry Changes in a TransactionReceive and VerifyPractice on your own: PostgreSQL playground