Lesson 7 of 8 · 5 min
Changing data and transactions
INSERT, UPDATE and DELETE without accidents, upserts, transactions and the ACID guarantees, and how to keep SQL injection out of your application.
Reading data is safe. Changing it is where mistakes cost something, so this lesson pairs each command with the habit that keeps it safe.
INSERT
INSERT INTO customers (name, email, city)
VALUES ('Sara Khan', 'sara@example.com', 'Hyderabad');
Always list the columns. If someone adds a column later, an insert without the list breaks or, worse, puts values in the wrong place.
Insert several rows at once:
INSERT INTO products (name, category, price) VALUES
('Desk lamp', 'Accessories', 1299.00),
('Ring light', 'Video', 2199.00);
PostgreSQL and SQLite can hand back the new row, which saves a second query:
INSERT INTO orders (customer_id, ordered_at)
VALUES (5, CURRENT_DATE)
RETURNING id;
UPDATE
UPDATE orders
SET status = 'shipped'
WHERE id = 102;
An UPDATE without WHERE changes every row in the table. Two habits prevent that:
- Write the
WHEREclause first. - Run it as a
SELECTbefore turning it into anUPDATE:
SELECT * FROM products WHERE category = 'Accessories'; -- check: 3 rows
UPDATE products SET price = price * 1.05 WHERE category = 'Accessories';
DELETE
DELETE FROM orders WHERE status = 'cancelled';
The same warning applies: no WHERE, no data. Many applications never delete important rows at all; they set a flag such as deleted_at instead, so mistakes can be undone and history is kept.
If other tables point at a row, the foreign key stops you deleting it, which is usually what you want. You can choose the behaviour when you create the key:
order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE
CASCADE deletes the order's items along with the order. Use it only where the child rows are meaningless without the parent.
Upsert: insert or update
"Insert this row, or update it if it already exists":
-- PostgreSQL and SQLite
INSERT INTO customers (email, name, city)
VALUES ('asha@example.com', 'Asha Rao', 'Mysuru')
ON CONFLICT (email) DO UPDATE SET city = excluded.city;
-- MySQL
INSERT INTO customers (email, name, city)
VALUES ('asha@example.com', 'Asha Rao', 'Mysuru')
ON DUPLICATE KEY UPDATE city = VALUES(city);
Transactions
Placing an order means creating the order and its items together. If the second step fails, you don't want half an order. A transaction groups statements so they all succeed or none do:
BEGIN;
INSERT INTO orders (id, customer_id, ordered_at) VALUES (105, 3, CURRENT_DATE);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (105, 2, 1);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (105, 4, 2);
COMMIT;
If anything goes wrong before COMMIT, run ROLLBACK and the database returns to exactly how it was.
ACID
Transactions in a relational database give four guarantees:
- Atomicity: all of the transaction happens, or none of it.
- Consistency: constraints hold before and after; a transaction can't leave broken data.
- Isolation: transactions running at the same time don't see each other's half-finished work.
- Durability: once
COMMITreturns, the data survives a crash or power cut.
SQL injection
When your application builds SQL from user input, never paste the input into the query string:
// Dangerous: an email of x' OR '1'='1 returns every customer
db.query(`SELECT * FROM customers WHERE email = '${email}'`)
Use parameters instead. The database receives the value separately and can never mistake it for SQL:
// Safe (node-postgres)
db.query("SELECT * FROM customers WHERE email = $1", [email])
Every database library supports parameters (? in MySQL and SQLite, $1 in PostgreSQL). ORMs such as Prisma, Sequelize and Drizzle use them for you.
Check yourself
- Write an
UPDATEthat marks order 103 as shipped. WhatSELECTwould you run first? - Raise the price of every Video product by 10%, inside a transaction. Check the result, then
ROLLBACK. Are the prices back? - Why is
ON DELETE CASCADEa good fit fororder_itemsbut a bad one fororders.customer_id?
More from Databases and SQL
- Lesson 1 · 6 minWhat is a database?Tables, rows and columns, primary and foreign keys, relational versus NoSQL databases, and what SQL actually is.
- Lesson 2 · 6 minCreating tablesCREATE TABLE, choosing data types, constraints that keep data valid, and building the shop database used for the rest of the course.
- Lesson 3 · 5 minReading data with SELECTChoose columns, rename them with aliases, calculate new values, remove duplicates, and understand the order SQL runs a query in.