UPDATE
The SQL statement that changes values in existing rows, choosing the rows with WHERE and the new values with SET.
An UPDATE without a WHERE clause rewrites every row in the table.
In more detail
Without a WHERE clause it changes every row in the table. Preview the target rows with a SELECT using the same predicate, then check the affected row count. SET stock = stock - 1 computes the new value inside the database, which avoids overwriting a concurrent change with a stale one.
Example
UPDATE products SET stock = stock + 10 WHERE id = 5;Related terms
- WHERE clause — The part of a query that keeps only the rows for which a condition is true.
- affected row count — The number of rows an INSERT, UPDATE or DELETE actually changed, reported back to the program that ran it.
- compare and set — An update that changes a row only if it still holds the value the program expects, and reports whether it did.
- transaction — A group of database changes that succeed or fail together, so the data is never left half-updated.
Taught in
- Updating And Deleting Safely — PostgreSQL, Indexing & Relational Data Modeling
- Transactions And ACID Properties — PostgreSQL, Indexing & Relational Data Modeling
- Isolation Levels And The Lost Update — PostgreSQL, Indexing & Relational Data Modeling