Every price became zero
A junior developer meant to change one product's price. They forgot one short word.
Every product in the table was set to £0 and the shop sold stock for nothing until lunch.
Changing data is easy. Changing only the data you meant is the skill.
By the end of this lesson
Note
I can add, change and remove rows safely.
Key words
| Word | What it means |
|---|---|
| INSERT | adds new rows to a table |
| UPDATE | changes values in rows that already exist |
| DELETE | removes rows from a table |
| Transaction | a group of changes that all happen or none do |
| Commit | make the changes in a transaction permanent |
| Rollback | undo the changes in a transaction |
The cells students fill in are left blank in this preview.
Work on a copy, never on the shop
You will change data in this lesson. The shop must stay as it is, so you make a copy first.
These Runs sign in, make your own database student_depot, and copy the four garden products into a table called shelf.
Run them again at any time to start over.
Accept the server's certificate
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the client
alias db='mariadb -h db -u student -pstudent -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make your own database
db "CREATE DATABASE IF NOT EXISTS student_depot"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Throw away an old copy
db "DROP TABLE IF EXISTS student_depot.shelf"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Copy the garden products
db "CREATE TABLE student_depot.shelf AS SELECT id, name, price, stock FROM shop.products WHERE category_id = 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
INSERT adds a row
INSERT INTO names the table and its columns. VALUES gives one bracket of values for each new row.
Give the values in the same order as the columns. A column you leave out gets its default, or NULL.
If a key rule is broken, the database refuses the row, as it did in the first lesson.
Add a pair of gloves
db "INSERT INTO student_depot.shelf (id, name, price, stock) VALUES (100, 'Garden Gloves', 6.50, 25)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
UPDATE changes rows, and WHERE picks which
UPDATE names the table. SET gives the new value. WHERE picks the rows.
Without WHERE, every row changes.
The safe habit has three steps. Write a SELECT with the same WHERE. Check it shows the rows you meant. Then change SELECT into UPDATE.
Step 1: look at the rows first
db "SELECT id, name, stock FROM student_depot.shelf WHERE stock < 15 ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Step 2: change exactly those rows
db "UPDATE student_depot.shelf SET stock = stock + 10 WHERE stock < 15"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Step 3: look again
db "SELECT id, name, stock FROM student_depot.shelf ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
DELETE removes rows, for good
DELETE FROM removes the rows that match its WHERE. Without a WHERE it empties the whole table.
There is no bin. The same three steps apply: SELECT first, then DELETE, then SELECT again.
Remove the gloves
db "DELETE FROM student_depot.shelf WHERE id = 100"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think an UPDATE or a DELETE touches only the row they have in mind. It touches every row that matches, and with no WHERE that is all of them. Write the WHERE first and check it with a SELECT.
The mistake, on the copy only
db "UPDATE student_depot.shelf SET stock = 0"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
See what it did
db "SELECT id, name, stock FROM student_depot.shelf ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A transaction is all or nothing
Moving £50 between two accounts is two changes. If the machine fails between them, £50 has vanished.
A transaction groups the changes. COMMIT makes them all permanent. ROLLBACK undoes them all.
Until you commit, nobody else sees the changes. The next Run sends four statements in one session: it starts a transaction, deletes every row of the copy, rolls back and counts.
Delete everything, then roll back
db "START TRANSACTION; DELETE FROM student_depot.shelf; ROLLBACK; SELECT COUNT(*) AS still_there FROM student_depot.shelf"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: a crash half way
| Step | Account 1 | Account 2 | Without a transaction | With a transaction |
|---|---|---|---|---|
| Start | £200 | £100 | Fine | Fine |
| After the first change | £150 | £100 | £50 is missing | Not yet permanent |
| Crash | £150 | £100 | Stays wrong | Rolled back to £200 and £100 |
| Both changes and COMMIT | £150 | £150 | Fine | Fine |
The cells students fill in are left blank in this preview.
See the default mode
db "SELECT @@autocommit AS autocommit"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Why the answer is 1
Note
Normally each statement is its own tiny transaction, committed at once. That is why a single UPDATE cannot be taken back. To get a rollback you must say START TRANSACTION first, in one session.