SQL · Lesson 5 of 7

INSERT, UPDATE, DELETE

Change data safely, including transactions.

• Intermediate
• 12 min read
• 3 objectives

Before this lessonLesson 4: Joins

Queries read data; three statements change it. Because they change data, the golden rule is: write the WHERE clause first, and run it as a SELECT to check what it matches before you UPDATE or DELETE.

From reading to changing

Until now every query only read data. Real applications also create, edit and remove it: signing up adds a row, changing your email edits one, closing an account deletes one. Those three actions are INSERT, UPDATE and DELETE. Because they change data permanently, they deserve extra care, which is why this lesson also covers transactions.

INSERT

INSERT INTO customers (name, country, age)
VALUES ('Margaret', 'US', 52);

INSERT INTO customers (name, country, age) VALUES
  ('Alan', 'UK', 41),
  ('Barbara', 'US', 47);

Always name the columns so the statement survives table changes. Columns you omit receive their default (or NULL, or an auto-generated id).

UPDATE

UPDATE orders
SET status = 'shipped'
WHERE id = 10;

UPDATE customers
SET age = age + 1
WHERE country = 'UK';

DELETE

DELETE FROM orders
WHERE status = 'refunded' AND total < 10;

DELETE removes rows; DROP TABLE removes the whole table. Many systems prefer a soft delete: add a deleted_at column and filter on it, so data can be recovered.

Transactions

A transaction groups statements so they succeed or fail together. Moving money must subtract from one account and add to another; if the second step fails, the first must be undone. Databases guarantee this through the ACID properties (atomicity, consistency, isolation, durability).

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- or ROLLBACK; to undo everything since BEGIN

Upsert

Sometimes you want "insert, or update if it already exists". PostgreSQL and SQLite support it:

INSERT INTO customers (id, name, country, age)
VALUES (1, 'Ada', 'UK', 37)
ON CONFLICT (id) DO UPDATE SET age = excluded.age;

Seeing a change happen

INSERT INTO customers (id, name, country, age) VALUES (4, 'Kim', 'KR', 29);
SELECT id, name FROM customers

id | name
---+------
1  | Ada
2  | Linus
3  | Grace
4  | Kim

UPDATE: always check your WHERE first

An UPDATE without WHERE changes every row. A safe habit is to write the same WHERE in a SELECT first, confirm it returns the rows you expect, and only then turn it into an UPDATE.

UPDATE orders SET status = 'paid' WHERE id = 12;
SELECT id, status FROM orders ORDER BY id

id | status
---+-------
10 | paid
11 | paid
12 | paid

DELETE works the same way

DELETE FROM orders WHERE status = 'refunded';
SELECT id, status FROM orders

id | status
---+-------
10 | paid
11 | paid

Why transactions matter

Moving money between accounts takes two steps: subtract from one, add to the other. If the program crashes between them, money vanishes. A transaction groups steps so either all succeed or none do. Put BEGIN first, COMMIT to keep the changes, or ROLLBACK to undo them.

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;
SELECT id, balance FROM accounts ORDER BY id

id | balance
---+--------
1  | 50
2  | 70

Key takeaways

• INSERT adds rows, UPDATE edits them, DELETE removes them.
• Always include a WHERE on UPDATE and DELETE; test it with SELECT first.
• Transactions make multi-step changes all-or-nothing (BEGIN, COMMIT, ROLLBACK).

-- Write your solution here