Modifying Data: INSERT / UPDATE / DELETE

◈ 4 cards

Populate rows three ways, change rows in place, and remove rows — with the WHERE clause as your safety rail, because a WHERE-less UPDATE or DELETE hits every row in the table.

Three ways to INSERT

INSERT adds rows, and it has three forms:

-- 1. full row, values in column order
INSERT INTO departments VALUES (6, 'Nursing', 'HSC', 900000);

-- 2. selected columns only (the rest take DEFAULT or NULL)
INSERT INTO students (student_id, name, admitted_year)
VALUES (99, 'Test Stu', 2024);

-- 3. insert the result of a query (bulk-populate from existing data)
INSERT INTO ab_students
SELECT student_id, name FROM students WHERE state = 'AB';

Form 2 is the safe default in real code: naming the columns means a later schema change (a new column) doesn’t silently shift your values into the wrong slots. Form 3 (INSERT … SELECT) is how you derive a table from existing data — e.g. an Alberta-only students table.

UPDATE and DELETE are set-oriented

UPDATE changes columns in every row the WHERE matches; DELETE removes every row the WHERE matches:

UPDATE products SET price = price * 1.10 WHERE wc_id = 1;   -- 10% raise, print-shop only
DELETE FROM order_lines WHERE order_id = 6;                 -- remove a cancelled order's lines

The mental model is not a loop — SQL applies the change to the whole qualifying set at once.

The most dangerous habit in SQL

The WHERE clause is what scopes an UPDATE or DELETE. Omit it and the statement applies to every row in the table:

UPDATE products SET price = 0;   -- ⚠ zeroes EVERY product's price
DELETE FROM students;            -- ⚠ deletes EVERY student

The professional discipline: write the WHERE as a SELECT first, eyeball exactly which rows match, then swap SELECT * for UPDATE/DELETE. There is no undo on a committed DELETE.