SQL / INSERTING, UPDATING, AND DELETING ROWS
Deleting rows and the missing WHERE disaster
Delete exactly the rows you meant to, recognize that a missing WHERE legally means every row, and use a transaction to check the count before committing.
What you will learn
- Read a missing WHERE as 'all rows': it is valid SQL, not an error the server blocks.
- Wrap deletes in BEGIN, check the reported row count, then COMMIT or ROLLBACK.
- Check for ON DELETE CASCADE before deleting parent rows; children go uncounted.
- Spot subquery columns that silently resolve to the outer table and match every row.
Understanding Deleting rows and the missing WHERE disaster
DELETE removes entire rows, so unlike UPDATE it names no columns: DELETE FROM cart_items is already a complete statement. The WHERE clause is an optional filter layered on top of it, and its absence is not a missing piece of syntax, it is the instruction 'every row in this table'. That is why no engine rejects it, warns about it, or asks you to confirm: the parser sees a well-formed statement and the planner sees a full-table scan it is perfectly willing to run.
The useful mental model is that a DELETE is a transaction's worth of per-row work, not a single switch. Each matching row is located through an index or a scan, checked against foreign keys, passed to any triggers, and marked dead, and none of that becomes final until COMMIT. This is why the transaction boundary, not carefulness, is the real safety net: inside BEGIN you can delete, read the row count, inspect what is left, and ROLLBACK. Under autocommit the same statement commits the instant it succeeds, which is what turns a slip into a disaster.
The row count the server reports is the number worth reading, and also the number that can mislead you. It counts only rows removed from the table you named, so a parent delete with ON DELETE CASCADE can report DELETE 2 while thousands of child rows vanish from tables you never mentioned. After a COMMIT there is no undo: the table is still there, empty, with its indexes and constraints intact, and getting data back means restoring a backup, which returns the whole table as of some earlier moment rather than just the rows you regret.
CREATE TABLE cart_items (
id integer PRIMARY KEY,
cart_id integer NOT NULL,
sku text NOT NULL
);
INSERT INTO cart_items VALUES
(1, 10, 'PEN-BLK'),
(2, 10, 'PAD-A5'),
(3, 11, 'PEN-BLK'),
(4, 12, 'MUG-RED');
BEGIN;
DELETE FROM cart_items; -- WHERE forgotten: this means all 4 rows
SELECT count(*) AS rows_left FROM cart_items;
ROLLBACK; -- the only undo that exists
DELETE FROM cart_items WHERE cart_id = 10; -- what was meant in the first place
SELECT count(*) AS rows_left FROM cart_items;A DELETE with no WHERE is not a mistake the database can detect, it is a valid request to remove every row, so the only dependable guard is an open transaction you can roll back.
Worked examples
A WHERE that filters nothing
A subquery column that does not exist in the inner table silently binds to the outer table, so the filter matches every row.
CREATE TABLE orders (id integer PRIMARY KEY, customer text);
CREATE TABLE cancelled (order_id integer);
INSERT INTO orders VALUES (1, 'ana'), (2, 'bo'), (3, 'cy');
INSERT INTO cancelled VALUES (2);
BEGIN;
DELETE FROM orders WHERE id IN (SELECT id FROM cancelled);
SELECT count(*) AS n FROM orders;
ROLLBACK;
DELETE FROM orders WHERE id IN (SELECT order_id FROM cancelled);
SELECT count(*) AS n FROM orders;Example explained
Line 1cancelled has no column called id, so inside the subquery id resolves outward to orders.id.
Line 2The subquery then yields one value per row of cancelled, always equal to the row being tested, so id IN (...) is true for all three orders.
Line 3DELETE 3 is the only signal that the filter was meaningless; the statement itself is legal SQL and raises nothing.
Line 4Using the real column name, order_id, keeps the reference inside the subquery and removes exactly one order.
The count that hides a cascade
A filterless delete on a parent table reports two rows while a cascading foreign key empties a table that was never named.
CREATE TABLE authors (id integer PRIMARY KEY, name text);
CREATE TABLE books (
id integer PRIMARY KEY,
author_id integer REFERENCES authors(id) ON DELETE CASCADE,
title text
);
INSERT INTO authors VALUES (1, 'ana'), (2, 'bo');
INSERT INTO books VALUES (10, 1, 'Roots'), (11, 1, 'Tides'), (12, 2, 'Ash');
BEGIN;
DELETE FROM authors;
SELECT count(*) AS n FROM books;
ROLLBACK;
SELECT count(*) AS n FROM books;Example explained
Line 1ON DELETE CASCADE on books.author_id makes every removed author drag its books out with it.
Line 2DELETE 2 counts only the authors rows the statement targeted, so the three deleted books never appear in that number.
Line 3count(*) on books returns 0 inside the transaction: a statement written against one table emptied another.
Line 4ROLLBACK reverses the cascaded child deletes as well, because they happened in the same transaction.
Important notes
A filterless DELETE on a large table is not cheap. Every row is located, locked, written to the log and left behind as dead space, so it can run for a long time and block other writers.
Rolling back only helps while the transaction is open. Once committed, recovery means a backup or point-in-time restore, and rows a cascade removed elsewhere have to be recovered along with it.
Common mistakes
Highlighting part of a statement in a GUI editor and executing DELETE FROM orders while the WHERE line sits unhighlighted below it: the table empties and autocommit has already made it permanent.
Expecting the database to refuse or prompt on a filterless DELETE. It reports success, and a client guard such as MySQL's safe-update mode does not exist in psql, sqlite3, or the deploy script that will run the same SQL later.
Typing ROLLBACK after the accident without ever having run BEGIN: Postgres replies WARNING: there is no transaction in progress, and the rows are still gone.
Try it yourself
Change, predict, then run
Create a table with three rows, run BEGIN followed by DELETE FROM with no WHERE, confirm count(*) is 0, then ROLLBACK and confirm the three rows are back. Finish by deleting exactly one row with a WHERE and checking that the reported count is 1.
Open the SQL workspaceCheck your understanding
A session runs BEGIN; then DELETE FROM invoices; and the server reports DELETE 4210. Nothing else has run. What is true at this moment?
- The 4210 rows are already permanently gone, because ROLLBACK cannot restore deleted rows.
- Other sessions already see invoices as empty, since the server reported the delete as done.
- The rows are still visible to other sessions, and a ROLLBACK here restores them for this session too.
- The statement was rejected safely because DELETE requires a WHERE clause, so 4210 is only an estimate of what would have gone.
Show answer
The rows are marked dead inside an open transaction, so nothing is final until COMMIT: ROLLBACK restores them and concurrent readers keep seeing the pre-delete state. Option 1 is tempting because the row count looks like a completion receipt, but that count reports work done inside the transaction, not visibility to anyone else.