Part 4 of 5 · 2 min read
UPDATE and DELETE: changing and removing rows
Modifying existing rows, and the danger of forgetting WHERE.
UPDATE and DELETE: changing and removing rows
Think of correcting one entry in a ledger, versus tearing one page out of it entirely. Both change the ledger. Only one removes a record completely.
In plain words: UPDATE changes the value of one or more columns in rows that match a condition. DELETE removes rows that match a condition entirely. Both statements, without a WHERE clause, apply to every row in the table. This is almost never what was intended, and is one of the most common real mistakes made against a live database.
A worked example. Correcting a customer's address after they move, and removing a cancelled order.
The key lines.
UPDATE customers SET address = '22 Church Street, Bengaluru' WHERE customer_id = 107;
DELETE FROM orders WHERE order_id = 9001;
UPDATE, DELETE
UPDATE changes column values in rows matching a condition. DELETE removes rows matching a condition. Leaving out the condition applies either statement to the whole table.
Example: UPDATE customers SET address = '...' WHERE customer_id = 107 changes one customer's address; removing the WHERE clause would change every customer's address to the same value.
Check yourself before running UPDATE or DELETE
Run the same condition as a SELECT first. If SELECT * FROM orders WHERE order_id = 9001 returns exactly the one row expected, the UPDATE or DELETE using that same condition is safe to run.
Quick notes
- SELECT reads rows; INSERT adds a row; UPDATE changes rows; DELETE removes rows.
- All four name a table. SELECT, UPDATE and DELETE usually also name a condition through WHERE.
- Leaving out WHERE on UPDATE or DELETE applies the statement to every row in the table.
- Testing the WHERE condition with a SELECT first is a cheap way to confirm it matches only the intended rows.
- Level 1 goes deep on each of these statements; this page only needs to be enough to read the SQL already shown in earlier modules.