Core CS · SQL

One missing WHERE changes every row

INSERT, UPDATE and DELETE each pick a set of rows and write it. This lesson is about how that set gets decided, which constraints look at it on the way through, and the one word that decides whether you can take any of it back.

Run eight statements, then take them all back →

UPDATE student SET marks = marks + 5; is a perfectly legal statement. It rewrites every row in the table, reports how many it rewrote, and returns without a question. The only thing that would have stopped it is a clause you did not type.

01 The idea

Every DML statement is a set of rows

A SELECT asks a question. INSERT, UPDATE and DELETE change what is stored, and those three together are the Data Manipulation Language, or DML. They are the statements you will type most often once you are past learning to query, and they are the only ones in this course that can destroy somebody else’s work, so they are worth slowing down for.

The thing to hold on to is that none of the three operates on a row. Each one operates on a set of rows, and that set is decided in full before a single byte is written. An INSERT builds its set from the values you supply, or from the rows a SELECT returns. An UPDATE and a DELETE build theirs by testing every row in the table against the WHERE clause and keeping the ones where the test came out true. Leave the WHERE clause out and there is no test, so nothing is filtered, and the set is the whole table.

That is why the most expensive mistake in SQL is a typing mistake rather than a logic one. Nothing about UPDATE student SET marks = marks + 5; is invalid. The server does not ask whether you meant it. It rewrites every row, hands back a count, and moves on. Whether you can undo that afterwards depends on something the statement itself never mentions: whether you were inside a transaction when you ran it.

Most clients run with autocommit on, which means the server wraps each statement in its own transaction and commits it the instant the statement returns. Type BEGIN first and it stops doing that. Every change you make from then on is held in the transaction’s undo, invisible to every other session, until you type COMMIT to keep it or ROLLBACK to throw it away. Two identical scripts, one word apart, and only one of them is survivable.

A DML statement changes a set of rows, never one row. On an UPDATE or a DELETE, WHERE is the only thing that narrows that set, and leaving it out means every row in the table. Until COMMIT, none of it is permanent.

DMLINSERT, UPDATE and DELETE: the statements that change the rows inside a table rather than the table itself. Every one of them can be rolled back, which is exactly what separates them from DDL.

Rows affectedThe count the server hands back after each statement. It is the only confirmation you get, and there is no separate warning for a statement that matched far more rows than you meant it to.

Referential actionThe ON UPDATE or ON DELETE clause on a foreign key. It tells the database what to do to the child rows when the parent key changes or disappears: reject the statement, follow the change, or blank the reference.

02 Worked example

Two tables, eight statements, and one that should never have run

One student table with four rows carries the rest of this lesson, and a second table, lab, holds a practical score for three of them. The lab rows earn their place twice over: a subquery reads them to decide who gets moderation marks, and a foreign key from lab back to student decides what a DELETE is allowed to do.

student · four rows

sid	name	marks
`S1`	Asha	`78`
`S2`	Ravi	`65`
`S3`	Meera	`35`
`S4`	Vikram	`52`

lab · foreign key to student

sid	score
`S1`	`9`
`S3`	`8`
`S4`	`6`

Eight statements run against those two tables, in this order. Section 04 steps through all eight and shows both tables after every one of them. Here is the shape of the run, and the node that ruins the afternoon.

1 · Two INSERTsS5 arrives with all three values given by position; S6 arrives with only two columns named, so his marks land as NULL

→

2 · UPDATE with a WHEREone row matched out of six, and the NULL row is not one of them

→

3 · UPDATE with no WHEREsix rows matched, six rewritten, no error and no warning of any kind

→

4 · A delete, a rename, a refusalone row goes; renaming a key drags the matching lab row along with it; the last DELETE is rejected whole, because lab still points at the row it names

→

5 · ROLLBACKfourteen row changes discarded, and student is the four rows it started with

Node 3 is the node. It differs from node 2 by one clause, it is accepted without a murmur, and the reported count jumps from 1 to 6. Nothing about it is invalid. marks + 5 is arithmetic the server is happy to perform on every row it can see, and the one row it cannot perform it on, the NULL row, it rewrites anyway with the same NULL that was already there.

The refusal at the end of node 4 is the part people expect to be dangerous, and it is the safe one. A DELETE that a foreign key refuses does not remove some rows and stop; it is rejected as a single unit and removes nothing at all. The constraint is not a warning you can ignore, it is a wall the statement bounces off.

Node 5 is what decides whether node 3 was a mistake or a disaster. Everything from node 1 onwards sat in the transaction’s undo, which is why one word puts the table back. Run the same eight statements with autocommit on and node 5 has nothing to work with: each statement committed itself as it returned, and the only route back is a restore from backup.

03 Mechanics

Which rows, which constraints, and what a rollback can take back

Six statement shapes, and the same three questions asked of each: which rows end up in the set, what gets checked on the way through, and what number comes back. The middle column is the one candidates skip. A constraint is not paperwork you satisfied once at design time; it runs on every row of every write, and it is the reason a statement you typed correctly can still be refused.

Statement	The set of rows it writes	What gets checked on it	What it reports
INSERT ... VALUES	`exactly the rows in the VALUES list`	Data type and width, NOT NULL and CHECK on every column of the new row, PRIMARY KEY and UNIQUE against the whole table, FOREIGN KEY on the child side. A column you did not name gets its declared default, and NULL if none was declared.	one per row in the list
INSERT ... SELECT	`whatever the SELECT returns, lined up with the target by position`	The same set, on every row the SELECT produced. The SELECT itself checks nothing; it only decides how many rows arrive at the target.	the SELECT’s row count. 0 is a normal answer, not an error
UPDATE ... WHERE	`the rows where the WHERE came out TRUE`	NOT NULL, CHECK, UNIQUE and PRIMARY KEY on the columns you actually changed, FOREIGN KEY on the child side, plus any ON UPDATE action on the parent side.	rows matched, which is not always rows changed
UPDATE with no WHERE	`every row in the table`	The same checks, on every row. If one row fails a CHECK the whole statement is rejected and nothing is written.	the table’s entire row count
DELETE ... WHERE	`the rows where the WHERE came out TRUE`	FOREIGN KEY on the parent side only. RESTRICT and NO ACTION reject the statement, CASCADE deletes the children too, SET NULL blanks their reference.	rows removed
DELETE with no WHERE	`every row in the table`	The same, row by row, which is why it is slow on a large table and why it is still undoable before COMMIT.	the table’s entire row count

Here are the two tables as they were declared. Two things in this schema decide most of what happens later: marks is nullable on purpose, and lab.sid carries two referential actions rather than none.

CREATE TABLE student (  sid    CHAR(2)     PRIMARY KEY,  name   VARCHAR(20) NOT NULL,  marks  INT         CHECK (marks BETWEEN 0 AND 100)   -- nullable on purpose: no mark yet is not a zero);CREATE TABLE lab (  sid    CHAR(2) PRIMARY KEY         REFERENCES student(sid) ON UPDATE CASCADE ON DELETE RESTRICT,  score  INT NOT NULL CHECK (score BETWEEN 0 AND 10));

Line 4 is the quiet one. A CHECK rejects a row only when the condition comes out false. For a NULL marks, marks BETWEEN 0 AND 100 comes out unknown, and unknown is not false, so the row is accepted. If you want to forbid a missing mark, the keyword is NOT NULL, not a tighter CHECK.

And here is the run itself, with the number of rows each statement reports. Assume you typed BEGIN before line 1, which is what gives the last line something to undo. Every count below is worked out row by row against the rows the table holds at that moment, six rows for lines 3 to 6 and five once line 7 has removed one; none of them is a guess.

INSERT INTO student VALUES ('S5', 'Neha', 41);                 -- 1 row · positional, by column orderINSERT INTO student (sid, name) VALUES ('S6', 'Dev');        -- 1 row · marks not named, so marks is NULLUPDATE student SET marks = marks + 5 WHERE marks < 40;      -- 1 row · S6 not matched: NULL < 40 is unknownUPDATE student SET marks = marks + 3  WHERE sid IN (SELECT sid FROM lab WHERE score >= 8);      -- 2 rows · the subquery returns S1 and S3UPDATE student SET marks = marks + 5;                       -- 6 rows · no WHERE, so every rowDELETE FROM student WHERE sid = 'S5';                       -- 1 rowUPDATE student SET sid = 'S9' WHERE sid = 'S3';              -- 1 row here, 1 row cascaded into labDELETE FROM student WHERE marks < 50;                     -- 0 rows · rejected, lab still points at S9ROLLBACK;                                                    -- 14 row changes discarded

Line 3 and line 6 are the same assignment. The only difference is seventeen characters of WHERE, and the difference in effect is one row against six. Line 5 is the shape to memorise for a subquery: the inner SELECT runs first, produces a list of key values, and the outer WHERE ... IN matches against that list. The subquery reads the table as it stood when the statement began, so an UPDATE can never chase its own changes around the table.

The other form of INSERT takes its rows from a query instead of a literal list. Nothing new is checked; the target’s constraints see every row the SELECT hands over, exactly as if you had typed them.

-- topper(sid, name, marks) is empty. student holds six rows at this point:--   S1 Asha 78 · S2 Ravi 65 · S3 Meera 35 · S4 Vikram 52 · S5 Neha 41 · S6 Dev NULLINSERT INTO topper (sid, name, marks)SELECT sid, name, marks FROM student WHERE marks >= 60;-- the SELECT returns S1 Asha 78 and S2 Ravi 65.-- S6 is not returned: NULL >= 60 is unknown, and WHERE keeps only TRUE.-- 2 rows inserted. Whatever the SELECT returns is what you get, zero included.

The columns line up by position, not by name. SELECT name, sid, marks into a target declared (sid, name, marks) is not a mistake the database can catch for you unless the types happen to disagree, and here they would: a name will not fit a CHAR(2). Rely on that and one day two same-typed columns will swap silently.

The last piece is what line 8 did to lab. Renaming a key in the parent table is not a change to one table; it is a change to every row anywhere that points at it. The ON UPDATE clause is where you decide, once, at design time, what that means.

Clause on lab.sid	UPDATE student SET sid = 'S9' WHERE sid = 'S3'	lab afterwards
ON UPDATE NO ACTION the default when you write nothing	`rejected`	unchanged: S1 9, S3 8, S4 6. The child row would have been orphaned, so the parent is not allowed to move.
ON UPDATE RESTRICT	`rejected`	unchanged. Same outcome, different timing: RESTRICT is checked immediately and cannot be deferred, NO ACTION is checked at the end of the statement and can be deferred to COMMIT where the database supports DEFERRABLE.
ON UPDATE CASCADE what this schema declares	`allowed`	S1 9, S9 8, S4 6. The database rewrites the child key to follow the parent, in the same statement, and counts it as part of the same transaction.
ON UPDATE SET NULL	`cannot apply here`	The child key would become NULL, which needs a nullable column. Here lab.sid is lab’s own PRIMARY KEY, so it can never hold NULL. MySQL refuses the table definition outright; PostgreSQL accepts the declaration and then fails the UPDATE when the action fires.
ON UPDATE SET DEFAULT	`fails on this schema`	The child key becomes the column’s declared default, and lab.sid declares none, so the default is NULL and this ends exactly where SET NULL ended. Declare a default and it must itself exist as a row in student, or the action violates the very constraint it is servicing. MySQL InnoDB parses this clause and then refuses the table definition.

Read any UPDATE or DELETE in two halves. The right half decides which rows, and it is three-valued logic, so unknown is not the same as false. The left half decides what is written to them. A missing WHERE is not a smaller statement. It is a statement about every row you own.

05 Cheat sheet

The whole of DML on one card

Six shapes, the count each one gives back, and the trap that gets people. The last column is the part worth rehearsing, because every one of those traps is a question somebody has been asked.

Statement	Rows affected	The trap
INSERT ... VALUES	`one per row in the VALUES list`	No column list means positional. An ALTER TABLE that adds or reorders a column silently changes what your statement means.
INSERT ... SELECT	`whatever the SELECT returns, 0 included`	Columns line up by position, not by name. One statement, so one constraint failure rejects the whole batch.
UPDATE ... WHERE	`rows where the test came out TRUE`	Unknown is not true. A row whose column is NULL is skipped by marks < 40 and by marks >= 40 alike.
UPDATE with no WHERE	`every row in the table`	No error, no warning, no prompt. The row count is the only signal you get that anything went wrong.
DELETE ... WHERE	`rows where the test came out TRUE`	A child row under RESTRICT or NO ACTION rejects the entire statement, not only the row it protects.
DELETE with no WHERE	`every row in the table`	Undoable before COMMIT, and logged row by row, so it is slow. TRUNCATE is faster and is DDL in Oracle and MySQL, which means an implicit COMMIT and nothing to roll back.

Run the SELECT firstBefore any UPDATE or DELETE on a table that matters, run SELECT * FROM t WHERE ... with exactly the same WHERE clause. The number of rows it returns is the number the write will report. If those two numbers disagree, you changed something between the two statements and should stop.

Unknown is not trueAny comparison against NULL evaluates to unknown, and WHERE keeps only rows that came out true. That is why a NULL row survives marks < 40, survives marks >= 40, and is not found by marks = NULL either, since NULL is not equal to anything, including itself. The only tests that find it are IS NULL and IS NOT NULL.

Provisional until COMMITEvery row change sits in the transaction’s undo until you commit it. Autocommit is on by default in MySQL, SQLite, SQL Server and psql; Oracle’s SQL*Plus waits for you to type COMMIT. And in Oracle and MySQL a DDL statement fires an implicit COMMIT on the way in, so an ALTER TABLE in the middle of your script ends the transaction whether you wanted it to or not.

06 Where & why

Where the undo actually lives

The statements are ANSI and they look identical everywhere. What differs between products is the part that decides whether a bad afternoon is recoverable: whether autocommit is on, whether a failed statement kills the transaction, and whether the foreign key you declared is even switched on.

PostgreSQL

Everything rolls back, including the schema

DDL is transactional here, so CREATE TABLE and ALTER TABLE come back with a ROLLBACK too, which is why migrations are usually written as one transaction. UPDATE ... RETURNING * hands back the rows it changed so you can read them before you commit. The trap is that one failed statement puts the whole block into an aborted state: every statement after it errors until you ROLLBACK or return to a SAVEPOINT.

MySQL · InnoDB

Autocommit is on, so the missing WHERE is already permanent

autocommit = 1 is the default, so a bare UPDATE is committed the moment it returns and there is nothing to roll back. Start the client with --safe-updates and it refuses any UPDATE or DELETE that has no WHERE and no key limit, which is one setting that has saved a lot of tables. MySQL also reports Rows matched and Changed separately, and ALTER TABLE commits your pending DML implicitly.

Oracle

Nothing is permanent until you type COMMIT

SQL*Plus does not autocommit, so DML stays provisional across as many statements as you like and other sessions see none of it. DDL still fires an implicit COMMIT before and after itself. MERGE does insert-or-update in a single statement, and flashback query lets you read a table as of a point in time before your mistake, which is the closest thing to an undo that survives a commit.

SQLite

One file, autocommit, and foreign keys off by default

Every statement is its own transaction unless you type BEGIN, and the whole database is one file, so a rollback is a file operation. The trap is PRAGMA foreign_keys = ON: it is per connection and off by default, so an ON UPDATE CASCADE you carefully declared is stored in the schema and never fires. INSERT OR REPLACE is also a delete followed by an insert, so it triggers ON DELETE actions people did not expect.

Two questions before any UPDATE or DELETE against a database somebody depends on. Which rows does this WHERE actually match, and is autocommit on? The first is a SELECT that takes three seconds to run. The second decides whether the answer to the first still matters a minute later.

07 Interview questions

What they actually ask

DML is where interviewers stop asking definitions and start asking what a statement returns. Most of these have a number for an answer, and the number is checkable, so a confident guess is worse than saying you would work it out row by row.

What counts as DML, and when does what it writes become permanent?

INSERT, UPDATE and DELETE, the statements that change the rows inside a table rather than the table itself. What they write becomes permanent at COMMIT and not before. With autocommit on, which is the default in MySQL, SQLite, SQL Server and psql, the server commits each statement for you the instant it returns, so the answer in practice is “immediately, unless you typed BEGIN first”.

You wrote INSERT INTO student VALUES ('S5', 'Neha', 41) with no column list. What does that depend on?

On the declared order of the columns in the table, and on supplying a value for each of them. It works today and it is fragile tomorrow: an ALTER TABLE that adds a column or changes the order makes the same statement either fail or, worse, put values in the wrong columns without complaining. Name the columns explicitly and the statement keeps meaning what you meant, whatever happens to the table.

What is INSERT ... SELECT, and how many rows does it insert?

It takes its rows from a query instead of a literal list: INSERT INTO topper (sid, name, marks) SELECT sid, name, marks FROM student WHERE marks >= 60;. It inserts exactly as many rows as the SELECT returns, and zero is a perfectly normal answer rather than an error. The SELECT’s columns line up with the target’s by position, not by name, and every row it produces is checked against every constraint on the target.

What happens if you leave the WHERE clause off an UPDATE?

Every row in the table is matched and rewritten. There is no error, no warning and no confirmation prompt, because nothing about the statement is invalid: you asked for a change with no restriction and you got one. The reported row count is the only signal you get, which is why the habit is to run the SELECT with the same WHERE first and check the count before you write anything.

Can an UPDATE use a subquery? Where can it go?

Yes, in two places. In the WHERE clause to choose the rows, usually with IN or EXISTS, as in WHERE sid IN (SELECT sid FROM lab WHERE score >= 8). And in the SET clause as a scalar subquery, to supply the new value from another table. The subquery reads the data as it stood when the statement began, so the update cannot chase its own changes. MySQL will not let the subquery read the same table you are updating; PostgreSQL and SQLite will.

DELETE FROM student WHERE sid NOT IN (SELECT sid FROM enrolment), where enrolment.sid is an ordinary nullable column and one of its rows holds NULL. How many rows go?

None. NOT IN against a list that contains a NULL can never come out true: for any candidate the comparison with the NULL is unknown, so the whole condition is unknown, and WHERE keeps only rows that came out true. The statement reports 0 rows and reads like a WHERE clause that found nothing to do. Write it as NOT EXISTS instead, which is unaffected, or filter the NULLs out of the subquery.

You delete a parent row that a child row references. What are your options?

Four, and you choose between them when you declare the foreign key. NO ACTION and RESTRICT reject the DELETE and nothing is removed. CASCADE deletes the child rows along with the parent. SET NULL keeps the child rows and blanks their reference, which needs a nullable column. SET DEFAULT points them at a declared default value, which must itself exist in the parent table.

What is the difference between ON DELETE RESTRICT and ON DELETE NO ACTION?

The outcome is the same, the timing is not. RESTRICT is checked immediately, as the row is deleted, and cannot be deferred. NO ACTION is checked at the end of the statement, and where the database supports DEFERRABLE INITIALLY DEFERRED it can be pushed all the way to COMMIT, which lets you fix up parent and child inside one transaction in any order. MySQL treats the two as identical, and Oracle has no RESTRICT keyword at all, so PostgreSQL is where the distinction actually pays off.

DELETE versus TRUNCATE versus DROP.

DELETE is DML: it removes rows one at a time, honours a WHERE clause, fires triggers, logs every row, and can be rolled back. TRUNCATE empties the whole table in one operation with no WHERE clause, is far faster because it does not log per row, and in Oracle and MySQL is DDL, so it fires an implicit COMMIT and there is nothing left to roll back. DROP removes the table itself, the rows, the indexes and the constraints together.

What is the difference between rows matched and rows changed?

A row is matched when the WHERE clause came out true for it, and changed when the value actually written differs from the value already there. UPDATE student SET marks = 78 WHERE sid = 'S1' on a row already holding 78 matches one row and changes none. MySQL reports both numbers; PostgreSQL and most others report the matched count only. Interviewers use this to see whether you know the count means “the WHERE found these” rather than “these are now different”.

Your UPDATE reported 0 rows. Is that an error?

Hard

The department wants a 5-mark moderation applied to every student with a lab score of 8 or more. The script will be run by an operator who is not certain whether it has already been run, and may well run it twice. Using only INSERT, UPDATE, DELETE and one transaction for the script itself, write a script that leaves the table in the same state whether it runs once or five times. Then state the one thing you had to add to the schema, and explain why no WHERE clause written over student alone can achieve it.

Follow-up

SET marks = marks + 5 is not repeatable, and you cannot make it repeatable by narrowing the WHERE, because once the first run has finished a moderated row is indistinguishable from a row that was always that high.

Show the hint

The second run has to be able to see that the first one happened. Decide where that fact is written down before you write a single statement.