Reading is safe; modifying is not. An UPDATE without WHERE rewrites the whole table, and there is no magic "undo" button in a database. So this article is half syntax, half safety procedure. Both halves are mandatory.

The three commands

INSERT — add a row:

INSERT INTO customers (name, email) VALUES ('Grigory', 'grig@example.com');

You list the columns and the values for them. id is usually omitted — the database assigns it itself.

UPDATE — change existing rows:

UPDATE orders SET status = 'paid' WHERE id = 102;

SET says what to change, WHERE — in which rows. Several fields at once is fine: SET status = 'cancelled', cancelled_at = now().

DELETE — remove rows:

DELETE FROM orders WHERE id = 103;

Rule number one: WHERE is not optional

UPDATE and DELETE share a property that has broken more data than everything else combined: without WHERE they apply to every row of the table. UPDATE orders SET status = 'paid' — and every order in the database is "paid." Syntactically it's a valid query; the database will execute it without blinking.

Hence a ritual worth turning into a reflex:

  1. SELECT first, with the same WHERE: SELECT * FROM orders WHERE id = 102; — look at exactly what will be affected. One row? The right one?
  2. Only now the UPDATE/DELETE — with the very same condition.
  3. Afterwards — a control SELECT: what changed is what you intended.

And watch the database's response: it always reports how many rows were affected. Expected "1 row," saw "5000" — something went wrong.

Transactions: a safety net with an undo button

A transaction executes several commands as one whole: all or nothing.

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE id = 102;
-- check: SELECT * FROM orders WHERE id = 102;
COMMIT;    -- all correct — make it permanent
-- or
ROLLBACK;  -- changed your mind — undo, as if nothing happened

Between BEGIN and COMMIT your changes are visible only to you — other database users see the old data. ROLLBACK cancels everything done since BEGIN. This is the "undo button" a standalone query lacks: wrap any manual change in a transaction, verify the result with a SELECT, and only then commit. Until you've said COMMIT, the way back is open.

Transactions were invented not for insuring manual edits but for integrity: a money transfer is "minus on one account" and "plus on another," and they must complete only together. But at your first stage, a transaction is precisely your safety rope.

Test environment and production

A simple rule: data is changed by hand on the test environment. There it's a normal part of the job — preparing data for a check, reproducing a rare state, rolling a scenario back. On the production database a manual edit is an emergency-grade event: approved, documented, and performed carefully (and not by a beginner). Before running a modifying query, make sure which database you're connected to — client tabs look identical, but the databases behind them differ.

Where this applies

Careful data modification turns you from an observer into the master of the environment: create a customer with the needed properties in seconds instead of walking through registration, move an order into a rare state to test a scenario unreachable from the UI, restore data after a destructive test. Preparing data with queries is one of the most visible work accelerators.

Where beginners stumble:

  • UPDATE/DELETE without WHERE — the classic, with whole-table consequences. The "SELECT first" ritual exists precisely for this.
  • A forgotten transaction without COMMIT. You made changes and left for lunch — nobody sees the changes, and the table may sit locked for colleagues. Started a transaction — finish it with COMMIT or ROLLBACK.
  • The wrong database. The query is perfect; the environment is not. Checking "where am I connected" takes two seconds and will one day save a career.

What to learn next. One last plot in the series: why some queries fly while others hang for minutes — indexes and slow queries.