Core · DatabasesLesson 5 of 6Free to read

Changing data and transactions · SQL and Databases

How do you change data safely?

Why you need it

Reading is safe; writing is where real damage happens. An UPDATE without WHERE changes every row, and there is no undo button. Transactions let several changes succeed or fail together, which is how a bank transfer never loses money halfway.

The intuition

INSERT INTO t (cols) VALUES (...) adds rows.
UPDATE t SET col = value WHERE ... changes rows.
DELETE FROM t WHERE ... removes rows.

A good habit: write the SELECT … WHERE … first, check it returns exactly the rows you mean, then turn it into UPDATE or DELETE with the same WHERE.

BEGIN; … COMMIT; wraps changes in a transaction. If anything goes wrong before COMMIT, ROLLBACK puts everything back.

Definition

Transaction

A group of statements that is applied all together or not at all. It starts with BEGIN and ends with COMMIT (keep) or ROLLBACK (throw away).

Constraint

A rule the database enforces, such as NOT NULL or PRIMARY KEY. A statement that breaks it fails with an error instead of storing bad data.

Examples

Insert, update, check

Run it in the lesson

Add a customer, move one, then look at the result.

1
2
3

INSERT INTO customers (id, name, city) VALUES (6, 'Fei', 'Chengdu');
UPDATE customers SET city = 'Paris' WHERE name = 'Dana';
SELECT name, city FROM customers WHERE id >= 4;

Output

name | city
Dana | Paris
Eli | NULL
Fei | Chengdu

Step by step

L1A sixth row appears.
L2Only rows matching WHERE change: just Dana.
L3Check the work.Dana | Paris

A transfer as a transaction

Run it in the lesson

Move stock between two shelves: both changes or neither. Here we roll back, so nothing changes.

CREATE TABLE stock (shelf TEXT PRIMARY KEY, units INTEGER);
INSERT INTO stock VALUES ('A', 10), ('B', 0);
BEGIN;
UPDATE stock SET units = units - 4 WHERE shelf = 'A';
UPDATE stock SET units = units + 4 WHERE shelf = 'B';
ROLLBACK;
SELECT * FROM stock;

Output

shelf | units
A | 10
B | 0

Step by step

L3From here on, changes are provisional.
L6Throw both updates away.
L7Still A 10 and B 0. With COMMIT instead, it would be 6 and 4.A | 10

Common mistakes

UPDATE without WHERE

UPDATE products SET price = 0; makes everything free. Always run the SELECT with the same WHERE first.

Building SQL from user input

Never glue user text into a statement. Use ? placeholders in your program; the Security course shows what goes wrong otherwise.

Quick quiz

1. What does this print?

1
2

UPDATE products SET price = price * 2 WHERE category = 'paper';
SELECT SUM(price) AS total FROM products;

A
```
total
54.0
```
B
```
total
59.0
```
C
```
total
108.0
```

Show the answer

Answer: B

Paper was 3.5 + 1.5 = 5.0 and becomes 10.0. The rest is 49.0, so the total is 59.0.

2. A transfer runs `UPDATE … - 4` and then the server crashes before the second UPDATE. With BEGIN … COMMIT around both, what is stored?

AOnly the first update
BNeither update
CBoth updates

Show the answer

Answer: B

Without COMMIT the transaction never finished, so the database discards it on restart.

Practice

3 exercises in three steps: predict what a program prints, change a program to meet a new requirement, then write one from scratch. Your code runs in the browser and is checked the moment you submit.

PredictCount after delete
Type exactly what this code returns.
ModifyDiscount one category
This UPDATE would cut every price. Make it apply only to the home category, then leave the final SELECT as it is.
WriteRecord a new order
Eli (customer 5) buys 3 Mugs (product 5) on 2025-03-08. Insert it as order 8 inside a transaction (BEGIN; … COMMIT;), then select the qty of Eli's orders.

Practise this lesson

Open the interactive lesson to edit and run these examples and solve the exercises with instant feedback. Free to start, no sign-up.

Open the interactive lesson Course map

Interactive lessons open in order. If you have not done the earlier ones yet, you start at the course map.

Terms in this lesson

Term	Chinese	Definition
Transaction	事务	Statements that take effect all together or not at all.
Rollback	回滚	Undoing every change made since BEGIN.
Constraint	约束	A rule the database enforces on stored data.