INSERT, UPDATE, DELETE

Why it matters

Reading data is half the job. Apps add new sign-ups, change order statuses and remove expired sessions all day, and analysts and administrators regularly fix bad records by hand. The statements are short, but they change real data, often with no undo. This lesson covers INSERT, UPDATE and DELETE, and the habits that keep them safe.

INSERT: add rows

Name the columns, then give the values in the same order:

INSERT INTO products (name, price, stock)
VALUES ('USB-C cable', 9.99, 120);

Always list the columns. INSERT INTO products VALUES (...) depends on the exact column order of the table, and breaks (or worse, puts values in the wrong columns) when someone adds a column. Columns you leave out get their default value, or NULL if there is no default. An INTEGER PRIMARY KEY column such as id is filled in automatically in SQLite.

Insert several rows in one statement:

INSERT INTO products (name, price, stock) VALUES
  ('Laptop stand', 34.00, 15),
  ('Webcam', 59.50, 8);

You can also insert the result of a query, for example to copy rows into an archive table:

INSERT INTO products_archive (id, name, price)
SELECT id, name, price FROM products WHERE discontinued = 1;

UPDATE: change existing rows

UPDATE products
SET price = 11.99
WHERE id = 3;

SET can change several columns, and can use the current values:

UPDATE products
SET price = ROUND(price * 1.10, 2),
    stock = stock + 50
WHERE id = 3;

The WHERE clause is the whole safety story. Without it, UPDATE products SET price = 11.99; changes every product. Most databases do exactly what you asked, immediately.

DELETE: remove rows

DELETE FROM products
WHERE discontinued = 1 AND stock = 0;

The same warning applies, more strongly: DELETE FROM products; empties the table. Deleted rows do not go to a recycle bin; getting them back means restoring a backup.

A safe routine for changing data

Professionals follow a routine, and so should you:

1. Write the WHERE as a SELECT first. SELECT * FROM products WHERE discontinued = 1 AND stock = 0; shows exactly which rows will change. Check the count.
2. Target by primary key when you can. WHERE id = 3 touches one known row. WHERE name = 'Webcam' might match two.
3. Turn the SELECT into the UPDATE or DELETE, keeping the same WHERE.
4. Use a transaction on real data: BEGIN; the change, check the result, then COMMIT; or ROLLBACK;. Transactions are covered in this module's last lesson.
5. Never practise on production. Use a copy, a local database or an exercise like this course's Do tab. In a job, changes to live data usually go through a reviewed script or migration, and many teams require a backup or a second person to check first.

Soft deletes

Many applications never truly delete important records. Instead they mark them:

UPDATE users SET deleted_at = '2024-06-01' WHERE id = 42;

Queries then filter with WHERE deleted_at IS NULL. The data can be restored, and audit history is kept. Personal data is the exception: privacy laws and user requests may require real deletion, and that is usually handled by a deliberate, tested process.

Seeing what changed

The database tells you how many rows an UPDATE or DELETE affected; command-line tools print it, and in code your library returns it. If you expected 1 and see 3,000, stop and roll back.

PostgreSQL and SQLite (3.35 and later) also support RETURNING, which hands back the changed rows:

UPDATE products SET stock = stock - 1 WHERE id = 3 RETURNING id, stock;

Constraints can refuse a change

An INSERT or UPDATE can fail because it breaks a rule in the schema: a NOT NULL column left empty, a duplicate value in a UNIQUE column, or a foreign key pointing nowhere. That is the database protecting you. Read the error message; it names the constraint. The next lesson shows how to define these rules.

Common mistakes

• No WHERE, changing or deleting every row.
• A WHERE that matches more than you think. Preview it with SELECT.
• Omitting the column list in INSERT.
• Text without quotes: SET status = shipped treats shipped as a column name. Write 'shipped'.
• Running experiments against live data.

Quick recap

• INSERT INTO t (cols) VALUES (...) adds rows; always list columns.
• UPDATE t SET col = value WHERE ... changes rows; DELETE FROM t WHERE ... removes them.
• Preview every WHERE with a SELECT, target by primary key, and wrap risky changes in a transaction.
• Consider soft deletes for records you might need again.

Try it

Open the Do tab and update a small product catalogue: add a product, raise a price and remove discontinued stock, then show the result.