INSERT adds rows, but most of the data in a database is already there and simply needs to change: salaries rise, people switch roles, a project's status flips from active to completed. Two statements handle nearly all of that work — UPDATE changes values in rows that already exist, and DELETE removes rows entirely.
Both are powerful, and both share one sharp edge: they operate on every row the WHERE clause matches — or, if you forget the WHERE, every row in the table. A missing WHERE on DELETE empties the table. So this lesson teaches the mechanics and, just as importantly, the habit of always naming exactly which rows you mean.
flowchart LR T["UPDATE employees"] --> S["SET salary = salary + 25000"] --> W["WHERE id = 10"] --> R["1 row changed"] style R fill:#0e7490,color:#fff
The anatomy of UPDATE
An UPDATE has three parts: the table, the SET that names the new values, and the WHERE that restricts which rows change:
UPDATE employees
SET salary = salary + 25000
WHERE id = 10;
The SET assigns a new value to a column — here salary becomes its current value plus 25000. The WHERE picks the rows; without it, the SET applies to every employee in the table, which is almost never what you want. Read the whole statement aloud and notice that the WHERE is what turns a company-wide event into a single person's raise.
UPDATE employees SET salary = salary + 25000 WHERE id = 10; SELECT name, salary FROM employees WHERE id = 10;
SET can read the old value and change several columns
The expression on the right of SET is evaluated using the row's current values, which is why salary = salary + 25000 adds to what is already there rather than replacing it with a fixed number. That matters whenever a change is relative — a percentage raise, a budget increase, a counter ticking up.
You may set several columns at once by separating them with commas. SET role = 'Senior Engineer', salary = 800000 changes both in a single pass, and the row is updated atomically — it never sits half-changed where other readers can see it.
UPDATE employees SET role = 'Senior Engineer', salary = 800000 WHERE id = 2; SELECT name, role, salary FROM employees WHERE id = 2;
Removing rows with DELETE
DELETE FROM takes rows away. You name the table and, as with UPDATE, restrict the damage with WHERE:
DELETE FROM projects
WHERE status = 'on hold';
This removes every project that is on hold and leaves everything else untouched. Note the wording — it is DELETE FROM projects, not DELETE * FROM projects; there is no column list because DELETE removes whole rows, not selected columns. If you ever want to clear a value without losing the row, that is an UPDATE ... SET col = NULL, not a delete.
DELETE FROM projects WHERE status = 'on hold'; SELECT name, status FROM projects ORDER BY id;
flowchart LR
U["UPDATE ... SET ..."] --> W{"WHERE?"}
W -->|"yes"| M["only matching rows change"]
W -->|"no"| ALL["every row changes"]
style M fill:#0e7490,color:#fff
style ALL fill:#b45309,color:#fff
A WHERE that matches nothing changes nothing
If your WHERE matches zero rows, the statement still succeeds — it simply affects nothing. UPDATE employees SET salary = 0 WHERE id = 999 runs cleanly and changes no rows, because no employee has id 999. That is correct behaviour, and it is also a quiet failure mode: a typo in the WHERE (the wrong column, a mis-typed number) silently does nothing instead of raising an error. This is why previewing with a SELECT first is not paranoia — it is the only way to know your WHERE actually matched the rows you intended before you commit the change.
flowchart LR INS["INSERT<br/>adds a row"] --> A["+1 row"] UPD["UPDATE<br/>changes a row"] --> C["same count, new values"] DEL["DELETE<br/>removes a row"] --> R["-1 row"] style A fill:#0e7490,color:#fff style C fill:#0e7490,color:#fff style R fill:#b45309,color:#fff
Give every employee in the Support department (department_id = 4) a raise of 30000.
UPDATE employees -- SET the new salary, WHERE department_id = 4 ;
Use
SET salary = salary + 30000to add to the current value.Restrict it with
WHERE department_id = 4.
UPDATE employees SET salary = salary + 30000 WHERE department_id = 4;
Promote Bo Hansen (id 2) to 'Senior Engineer' and set salary to 800000 in a single UPDATE.
UPDATE employees -- set two columns, separated by a comma WHERE id = 2 ;
Separate two assignments with a comma:
SET role = ..., salary = ....Keep the
WHERE id = 2so only Bo changes.
UPDATE employees SET role = 'Senior Engineer', salary = 800000 WHERE id = 2;
This UPDATE was meant to raise only Ivan Petrov's salary (id 9), but it has no WHERE clause — so it raises everyone. Add the WHERE so only id 9 is affected.
UPDATE employees SET salary = salary + 50000;
Without WHERE, every row gets the raise.
Add
WHERE id = 9to restrict it to Ivan.
UPDATE employees SET salary = salary + 50000 WHERE id = 9;
Remove every project whose status is 'completed'.
DELETE FROM projects -- which rows should the WHERE match? ;
DELETE FROM projects with a WHERE on status.
Text values are quoted:
WHERE status = 'completed'.
DELETE FROM projects WHERE status = 'completed';
Transfer skill: double the budget of every active project. Multiply the current budget by 2, and only touch rows whose status is 'active'.
UPDATE projects -- set budget to budget * 2, for active projects ;
Read the old value with
budget * 2.Restrict to active projects with
WHERE status = 'active'.
UPDATE projects SET budget = budget * 2 WHERE status = 'active';
Recap
UPDATE table SET col = expr WHERE ...changes values in existing rows; the right side can read the current value.- Set several columns at once by separating assignments with commas.
DELETE FROM table WHERE ...removes whole rows — no column list, no star.- The
WHEREis a safety gate: leave it off and you change or delete every row. - A WHERE that matches nothing changes nothing — preview it with a SELECT first.
Next you will meet the statement that combines insert and update into one: insert if the row is new, update it if it already exists.