INSERT, UPDATE & DELETE

Everything in this course so far has read. INSERT is the first statement that changes anything.

INSERT INTO products (name, category, price, launched_on)
VALUES ('Archive Pro', 'addon', 15.00, DATE '2024-06-01');

Name the columns. It is tempting to leave the list off and rely on column order, and it breaks silently the day somebody adds a column in the middle — values land in the wrong fields, with no error if the types happen to line up.

product_id is not in the list because the table generates it. A statement that changes rows returns no rows, so what you get back is a count rather than a result grid.

Nothing you do here can break anything. The database is rebuilt from its seed before every single run.

Your task: add a product called Archive Pro, category addon, price 15.00, launched 2024-06-01.

The row now exists, but you do not know its product_id — the database chose it. Selecting it back by name is a guess: two products could share a name.

RETURNING hands back the rows the statement actually wrote, generated columns included:

INSERT INTO products (...) VALUES (...)
RETURNING product_id, name;

This is a Postgres speciality and worth knowing. It works on UPDATE and DELETE too — RETURNING on a DELETE gives you what you just removed, which is the cheapest possible undo log.

Your task: insert the same product again, returning product_id and name.

VALUES is for rows you are typing. When the rows come from the database itself, a SELECT goes where the VALUES would be — no brackets, no VALUES keyword.

INSERT INTO subscriptions (user_id, plan, mrr, started_on)
SELECT user_id, plan, 0, signup_date FROM users WHERE ...;

The columns have to line up by position and type, the same rule as UNION in module 3-04. It is how a backfill is written: one statement, however many rows the query finds.

Your task: two users have no subscription row at all. Give each of them one, on their own plan, with mrr of 0 and started_on equal to their signup_date. Use NOT EXISTS to find them, from module 3-05.

UPDATE sets columns on the rows a WHERE selects. The SET can compute from the row's own current values, so a percentage rise needs no arithmetic on your part.

UPDATE products
SET price = price * 1.10
WHERE category = 'addon';

The WHERE is the same clause you have written since module 1-02, and it does the same thing — it decides which rows. The only difference is that the consequence is now permanent.

Your task: raise the price of every addon by 10%.

An UPDATE without a WHERE is valid SQL. It changes every row in the table, reports how many, and offers no warning of any kind.

UPDATE products SET price = 0;   -- 8 rows. The whole catalogue is free.

The same is true of DELETE FROM orders; — no error, no confirmation, just an empty table. This is the single most expensive mistake in everyday SQL, and the habits that prevent it are small:

Your task: the careful version. Set price to 0 for the single product nobody has ever ordered — and nothing else. Its name is Archive Add-on.

Sometimes the new value depends on a different table. UPDATE … FROM lets you join inside the update: the FROM names the other table, and the WHERE carries the join condition as well as the filter.

UPDATE products p
SET price = price * 2
FROM orders o
WHERE o.product_id = p.product_id
  AND o.status = 'refunded';

One caution the syntax hides: if the join matches a product several times, the row is still updated once, using an arbitrary one of the matches. It does not double twice. When the new value depends on which match won, aggregate in a subquery first rather than trusting the join.

Your task: two orders were refunded. Mark the products they were for by setting their category to review.

DELETE takes a WHERE and removes the rows it selects. There is no SET, because there is nothing to set — which makes the missing-WHERE version even shorter, and even easier to run by accident.

DELETE FROM orders WHERE status = 'refunded';

DELETE FROM orders; — six more characters removed — empties the table.

There is also TRUNCATE, which empties a table far faster by not touching the rows individually. It cannot be filtered, and in most databases it cannot be rolled back. Postgres is unusual in allowing TRUNCATE inside a transaction.

Your task: delete the refunded orders.

Try to delete a user and the database stops you:

DELETE FROM users WHERE user_id = 11;
ERROR:  update or delete on table "users" violates foreign key
        constraint "subscriptions_user_id_fkey" on table "subscriptions"

That is a foreign key doing its job. Rows in subscriptions and events point at that user, and removing them would leave those rows referring to nothing. The database refuses rather than allowing the orphan.

This is the constraint working, not an obstacle. Delete the children first, deepest first, and the parent goes last — and doing it in one statement per table, in the right order, is the whole technique.

A table can be declared ON DELETE CASCADE, which makes the children disappear automatically. Convenient, and worth being nervous about: one DELETE can then quietly remove thousands of rows from tables you were not thinking about.

Your task: remove user 11 completely — their events, their subscription, then the user. Three statements, in an order that never orphans anything.

A transaction groups statements so they succeed or fail together. Between BEGIN and COMMIT nothing is permanent, and ROLLBACK discards the lot.

BEGIN;
DELETE FROM orders;          -- 24 rows. Everything.
ROLLBACK;                    -- ...and nothing happened.

This is the real safety net for a risky change: BEGIN, run the statement, SELECT to see what it did, then COMMIT or ROLLBACK having actually looked.

Note that the rolled-back DELETE still reports 24 rows affected. The count says what the statement did, not what survived — so it is a useful check before you commit, and no evidence at all that anything persisted.

Transactions are also all-or-nothing across a failure. If the third of three statements errors, the first two are undone too, which is what stops a half-finished migration.

Your task: inside a transaction, delete every order, then roll it back so all 24 survive.