Modifying Data: INSERT / UPDATE / DELETE

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.