The WHERE of an UPDATE is optional. Without it, the UPDATE changes every row.
text
UPDATE products SET price = 12.00;products
| id | name | price |
|---|---|---|
| 1 | Kraken Plush | 12.00 |
| 2 | Pod Golem Plush | 12.00 |
| 3 | Daemon Cat Plush | 12.00 |
| … | … | … |
| 12 | Mystery Box | 12.00 |
PostgreSQL does not ask "are you sure?".
The old prices are gone, and no SQL statement brings them back. Chapter 14 shows a way to undo a group of changes before they become permanent. In these labs, Reset rebuilds the data. A real database has no Reset button.
Preview, then change
Before an UPDATE, run a SELECT with the same WHERE. It shows the rows that the UPDATE will change.
SELECT * FROM products WHERE category_id = 41. look
2 rows: correct
UPDATE products SET … WHERE category_id = 42. change
Copy the WHERE. Do not type it again: a small change in the text gives different rows.
Read the count
After an UPDATE, the output shows how many rows it changed:
text
2 rows updatedCompare it with the number of rows that the SELECT returned. If they are different, stop and find out why.