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.
INSERT: adding rows
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.
UPDATE: changing rows
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.
DELETE: removing rows
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").
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:
excluded means "the row you tried to insert". MySQL writes this as ON DUPLICATE KEY UPDATE.
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:
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: 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.
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)
Insert a product named Laptop stand in the Furniture category at price 79 (let the database choose the product_id).
Raise the price of every product in the Books category by 10%, rounded to 2 decimals. Other products must not change.
Delete the order_items rows that belong to cancelled orders. (Hint: a subquery finds the cancelled order IDs.)
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).
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?