CSE Roadmap

Core tier · about 2 hours · 5 parts

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.