Update Selected Rows
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 17 of 22.
UPDATE changes existing rows. SET assigns new values and WHERE limits which rows change. Without WHERE every row is eligible. RETURNING shows the updated values.
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 |
UPDATE products SET stock = stock + 2 WHERE id = 4 RETURNING id, stock;Mug stock changes from 8 to 10. The expression reads the old stock to calculate the new value. A precise condition is essential because UPDATE is a stored change, unlike a SELECT expression.
Use SET to calculate new values and WHERE to restrict UPDATE. RETURNING exposes the new row values.
Challenge
MediumReceive five extra units of product id 2. Update its stock and return id and stock.
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