SQL: INSERT, UPDATE and DELETE

A database is not a museum exhibit — it changes all the time. A new pupil joins the school, someone moves to a different house, another leaves at the end of the year. The SELECT command you already know only reads data; it never touches it. To actually change what is stored, SQL gives you three more commands:

INSERT — add a brand-new record (row).
UPDATE — change values in records that already exist.
DELETE — remove records.

Every example on this page works on the same little pupils table. Here is its starting state — four columns and three rows:

id	name	year	house
1	Aisha	9	Oak
2	Ben	10	Elm
3	Chloe	9	Oak

The id column gives every pupil a unique number (its primary key), so we can always point at exactly one record. Watch that column closely as records are added and removed.

INSERT — add a new record

INSERT puts a whole new row into a table. You name the columns you are filling in, then give a matching list of VALUES in the same order:

INSERT INTO pupils (id, name, year, house) VALUES (4, 'Dev', 10, 'Elm');

The number of values must match the number of columns, and each value must be the right kind of thing: numbers for id and year, and text in single quotes ('Dev', 'Elm') for the words. After this runs, the table gains a fourth row:

Notice that INSERT never has a WHERE clause — you are not choosing existing rows to act on, you are creating a new one, so there is nothing to filter.

You don't have to fill in every column. If you write INSERT INTO pupils (id, name) VALUES (5, 'Eshan'); then the columns you skipped get whatever the table says is their default — often the special value NULL, meaning "no value yet". That is fine for optional details, but a column marked NOT NULL (like a required id) will refuse the insert if you leave it blank.

UPDATE — change existing records

UPDATE changes values in rows that are already in the table. You say which table, use SET to give the new value, and — this is the important part — use WHERE to say which rows to change. Suppose Chloe switches from Oak to Elm house:

UPDATE pupils SET house = 'Elm' WHERE name = 'Chloe';

The database first finds every row that matches the WHERE condition, then applies the SET change to just those rows. Here only Chloe's row matches, so only her house cell changes — everything else is left exactly as it was:

You can change several columns at once by separating them with commas, e.g. SET year = 10, house = 'Elm', and you can match many rows at once — for example WHERE year = 9 would move every Year 9 pupil.

DELETE — remove records

DELETE FROM removes whole rows. Again, the WHERE clause decides which ones. To remove the pupil with id 2 (Ben):

DELETE FROM pupils WHERE id = 2;

The row is gone for good. Notice you don't list any columns — you can't delete half a record, only the whole row:

Deleting by the primary key (WHERE id = 2) is the safest way to remove exactly one record, because the id is guaranteed to be unique. Matching on a name or a house could accidentally sweep up several rows that happen to share that value.

No. DELETE FROM pupils empties out rows, but the table — its name and its columns — stays behind, ready for new records. A completely different command, DROP TABLE pupils, is the one that destroys the whole table structure. It is easy to confuse the two words, so read a destructive command twice before you run it.

Watch the changes happen

Our sandbox runs JavaScript/TypeScript rather than SQL, but the ideas map across perfectly: a table is a list of records, INSERT is adding one to the list, UPDATE is changing matching ones, and DELETE is filtering matching ones out. Run this to see the table before and after all three commands — then try changing the conditions.

type Pupil = { id: number; name: string; year: number; house: string }; let pupils: Pupil[] = [ { id: 1, name: "Aisha", year: 9, house: "Oak" }, { id: 2, name: "Ben", year: 10, house: "Elm" }, { id: 3, name: "Chloe", year: 9, house: "Oak" }, ]; const show = (label: string) => { console.log(label); for (const p of pupils) console.log(" " + p.id + " | " + p.name + " | yr" + p.year + " | " + p.house); }; show("BEFORE:"); // INSERT INTO pupils VALUES (4, 'Dev', 10, 'Elm') pupils.push({ id: 4, name: "Dev", year: 10, house: "Elm" }); // UPDATE pupils SET house = 'Elm' WHERE name = 'Chloe' for (const p of pupils) if (p.name === "Chloe") p.house = "Elm"; // DELETE FROM pupils WHERE id = 2 pupils = pupils.filter((p) => p.id !== 2); show("AFTER:");

The one clause you must never forget

WHERE is optional in the SQL grammar — the database will happily run an UPDATE or DELETE without it. That is exactly what makes forgetting it so dangerous.

An UPDATE or DELETE with no WHERE clause affects EVERY row in the table.

UPDATE pupils SET house = 'Elm'; -- changes ALL pupils' house to Elm DELETE FROM pupils; -- empties the ENTIRE table

The database does not ask "are you sure?" — it just does it, instantly, to all million rows if that is how many there are. There is no undo button. Always include a precise WHERE (matching the primary key when you mean a single record), and read the statement one more time before you run it. Professionals often run a SELECT with the same WHERE first, to see exactly which rows they are about to change.