sql · level 3 · unit 6 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
hands back the rows a change touched.
After an INSERT, you often need the new row's id, which the database chose. After an UPDATE, you want the new values. Asking again with a second SELECT costs another trip, and it can pick up the wrong row.
Add RETURNING and a list of columns to the end of an INSERT, UPDATE or DELETE. The statement then hands back the rows it changed, like a receipt. RETURNING * gives every column.
One warning: when several rows change, the order of the returned rows is not promised.
INSERT INTO books (title, price, stock)
VALUES ('Ocean Cats', 7, 3)
RETURNING id, title;RETURNING shows the row after the change, including values the database filled in, like the id.
guess first
A price rise and a sale at once. What comes back?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
UPDATE books SET price = price + 2, stock = stock - 1
WHERE title = 'Night Train'
RETURNING title, price, stock;your turn
Your turn. Clear the sold-out shelf.
Delete every book with no copies left, and return what you removed. Write the condition, then choose the columns to return, including title.
result
Your answer appears here as a table.
fix it
The restock receipt is in the wrong place. Fix it!
result
Your answer appears here as a table.
- The error points at
WHERE. Which part has to come last in the statement? RETURNINGalways goes at the very end, after theWHERE.- Move RETURNING title, stock below the WHERE line, and keep the semicolon at the end, then press run.
nailed it
every change hands you a receipt. :D
RETURNINGhands back the rows a change touched.- It goes at the very end of
INSERT,UPDATEorDELETE. - Several returned rows come back in no promised order.
new words in your collection
- RETURNING
This lesson isn’t saved. A free account remembers every lesson you finish.