Every price became zero
A new worker at an online shop wants to change the price of one pen. They forget one short line of the command.
Every price in the shop becomes 0. Customers buy things for nothing until somebody notices.
Changing data is easy. Changing only the data you meant to change is the skill.
By the end of this lesson
Note
I can change data without losing any.
Key words
| Word | What it means |
|---|---|
| INSERT | adds a new record |
| UPDATE | changes records that are already there |
| DELETE | removes records |
| Transaction | a set of changes that all happen or none do |
| COMMIT | keeps the changes of a transaction |
| ROLLBACK | undoes the changes of a transaction |
The cells students fill in are left blank in this preview.
Practise on a copy, never the shop
This lesson changes data, so you will work on a copy. The shop stays as it was.
The Runs below sign in, make a database called student_till and copy the four stationery products into a table called stationery.
Let the terminal trust the server
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 shop
alias sql='mariadb -h db -u student -pstudent shop -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
sql "CREATE DATABASE IF NOT EXISTS student_till"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for it
alias till='mariadb -h db -u student -pstudent student_till -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Start clean
till "DROP TABLE IF EXISTS stationery"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Copy the stationery products
till "CREATE TABLE stationery AS SELECT id, name, price, stock FROM shop.products WHERE category_id = 7"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Look first, then change, then look again
INSERT INTO ... VALUES adds a record. UPDATE ... SET ... WHERE changes the records that pass the WHERE. DELETE FROM ... WHERE removes them.
The safe habit: run a SELECT with the same WHERE first. It shows exactly which records the change will touch.
Add a new product
till "INSERT INTO stationery (id, name, price, stock) VALUES (300, 'Highlighter Pack', 3.20, 40)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Which products have fewer than 20?
till "SELECT id, name, stock FROM stationery WHERE stock < 20 ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A delivery of 12 for each
till "UPDATE stationery SET stock = stock + 12 WHERE stock < 20"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Look again after the change
till "SELECT id, name, stock FROM stationery ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Remove the new product
till "DELETE FROM stationery WHERE id = 300"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 changes only the record they have in mind. With no WHERE, it changes every record in the table. Write the WHERE first, and check it with a SELECT.
A transaction is all or nothing
START TRANSACTION opens a set of changes. COMMIT keeps them. ROLLBACK throws them all away.
The next Run makes the hook's mistake on purpose: every price becomes 0. It shows the prices, rolls back, then shows them again. It sends five statements at once, so they share one session.
Break every price, then roll back
till "START TRANSACTION; UPDATE stationery SET price = 0; SELECT name, price FROM stationery ORDER BY id; ROLLBACK; SELECT name, price FROM stationery ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Going further
Outside a transaction, each statement is kept the moment it runs. This is called autocommit.
Run till "SELECT @@autocommit AS autocommit". A 1 means it is on. So a single UPDATE with no transaction cannot be rolled back.