SQL & Databases
Topic 11 · Building databases

Changing Data: INSERT, UPDATE, DELETE & Transactions

Add, change and remove rows safely: INSERT (one row, many rows, or from a query), UPDATE and DELETE with WHERE, "insert or update" (upsert), and transactions that make several changes all-or-nothing.

Show:
In this topic
Beginner

INSERT: adding rows

INSERT INTO products (name, category, price) VALUES ('Laptop stand', 'Furniture', 79.0); INSERT INTO products (name, category, price) VALUES ('Webcam', 'Electronics', 89.0), ('Highlighters', 'Stationery', 6.5);

Always list the columns: it documents what goes where, and the insert keeps working if a column is added later. Columns you leave out get their DEFAULT (or NULL), and an INTEGER PRIMARY KEY is numbered automatically. INSERT INTO t (…) SELECT … copies the result of a query into a table.

Beginner

UPDATE: changing rows

UPDATE products SET price = ROUND(price * 1.05, 2) -- a 5% price rise… WHERE category = 'Books'; -- …only for books

The WHERE clause is everything. UPDATE products SET price = 0; without WHERE changes every row. Good habit: write the WHERE as a SELECT first, check it returns exactly the rows you mean, then turn it into the UPDATE.

Beginner

DELETE: removing rows

DELETE FROM orders WHERE status = 'cancelled' AND order_date < '2025-02-01';

Same rule: no WHERE means every row is deleted. Foreign keys may stop you deleting a row that other rows still point to, which is usually a good thing. Many real systems never delete; they mark rows as inactive instead (a "soft delete").

Beginner

Upsert: insert, or update if it exists

"Add this product, or if its key already exists, update it" is called an upsert. SQLite and PostgreSQL write it with ON CONFLICT:

INSERT INTO products (product_id, name, category, price) VALUES (3, 'USB-C hub', 'Electronics', 45.0) ON CONFLICT (product_id) DO UPDATE SET price = excluded.price;

excluded means "the row you tried to insert". MySQL writes this as ON DUPLICATE KEY UPDATE.

Beginner

Transactions: all or nothing

Moving $100 between two accounts is two updates. If the program crashes after the first, money vanishes. A transaction groups statements so they either all happen or none do:

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- make both permanent (or ROLLBACK; to undo both)

Databases guarantee this with the ACID properties: Atomic (all or nothing), Consistent (constraints always hold), Isolated (others never see half-done work) and Durable (committed changes survive a crash). Python's sqlite3 and most libraries start transactions for you, which is why you must call commit().

Try it

Try it: change the shop

Run the presets in order and watch the data change. Press Reset the database to start again.

Interactive: this part runs in your browser.

Beginner

Practice: check yourself

Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.

Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)

Exercise 1. Add a product

Insert a product named Laptop stand in the Furniture category at price 79 (let the database choose the product_id).

Exercise 2. A price rise for books

Raise the price of every product in the Books category by 10%, rounded to 2 decimals. Other products must not change.

Exercise 3. Remove cancelled items

Delete the order_items rows that belong to cancelled orders. (Hint: a subquery finds the cancelled order IDs.)

Beginner

Summary

Key words

INSERT
adds rows
UPDATE … SET … WHERE
changes the matching rows
DELETE … WHERE
removes the matching rows
upsert
insert, or update if the key exists (ON CONFLICT)
transaction
statements that succeed or fail together
COMMIT / ROLLBACK
make permanent / undo a transaction
ACID
the guarantees of a transaction

Common mistakes

  • UPDATE or DELETE without WHERE: every row changes.
  • Not checking the WHERE with a SELECT first.
  • INSERT without a column list, which breaks when the table changes.
  • Forgetting to commit, so the changes vanish when the connection closes.
  • Building INSERT statements by gluing strings in code: use parameters (see SQL from Python).
Check yourself

Quiz

1. What does DELETE FROM orders; do?

2. How do you safely prepare an UPDATE?

3. What does ROLLBACK do?

4. What is an upsert?

Ready