UPDATE, DELETE & Returning
Overview
UPDATE modifies existing rows in a table, and DELETE removes them entirely. Both use the WHERE clause to target specific rows. Similar to INSERT, both commands support a RETURNING clause to immediately fetch the modified or removed data, providing instant confirmation of the operation's effect.
Data changes over time. You need UPDATE to correct or evolve data and DELETE to remove obsolete records safely. RETURNING helps verify what was actually affected.
Where used: soft or hard deleting accounts, updating order statuses, adjusting prices
Why learn this
- You will know how to modify or remove precisely the data you intend.
- You can chain RETURNING with application logic to log what changed without extra queries.
Query walkthrough
UPDATE orders SET status = 'cancelled' WHERE id = 3 RETURNING id, status;Focus: Filtering down to order id 3, applying the status modification, and returning the updated state.
Common mistakes
- Forgetting the WHERE clause: Running UPDATE or DELETE without WHERE affects EVERY row in the table, which can destroy production data. Always double-check your WHERE conditions.
- Incorrect WHERE condition: Using a condition that is too broad, inadvertently updating or deleting more rows than intended.
Glossary
- obsolete
- No longer needed, useful, or relevant, usually because it has been replaced by something newer.
- inadvertently
- Doing something accidentally or without intending to, like mistakenly deleting the wrong data.
Recall questions
- What happens if you omit the WHERE clause in an UPDATE statement?
Understanding checks
Assuming a product row exists where id = 6 and price = 4.99, what rows does this query return?
id: 6, price: 9.99
It updates the Notebook (id 6) price from 4.99 to 9.99 and returns the new state.
Assuming a customer row exists where id = 8 and name = 'Hana', what rows does this query return?
name: 'Hana'
It deletes the row for Hana (id 8) and returns the name of the removed row.
Practice tasks
Update product price
Given this UPDATE statement, modify it to increase the price of the 'Keyboard' (id 1) to 59.99, and return the new price.
Challenge
Delete and Return
Write a query to delete all orders that are currently 'pending' (you can find the status in the orders table) and return their IDs.
Continue learning
Previous: WHERE & Operators