INSERT, UPDATE and DELETE: Change Data Safely

Until now you only read data. Real work also means changing it: adding a new customer, fixing a wrong price, removing a test row. These statements are powerful. A single careless UPDATE without a condition can change every row in a table, and in a live system that can mean a very bad day. This lesson teaches the three statements and, more importantly, the habits that keep you safe.

INSERT: add a new row

INSERT INTO customers (name, city, joined)
VALUES ('Ivy Dube', 'Durban', '2024-07-01');

You name the table, then the columns in brackets, then the values in the same order. You do not give id: because it is an integer primary key, SQLite picks the next free number, which is 9. Check the result.

SELECT id, name FROM customers WHERE id >= 8;

id | name
8  | Hari Singh
9  | Ivy Dube

Always write the column list. INSERT INTO customers VALUES (...) without names depends on the exact column order of the table, and it breaks the day someone adds a column. To add several rows at once, list several value groups separated by commas, as the setup script does.

Columns you leave out get their default value, which is NULL unless the table says otherwise. A column marked NOT NULL with no default must be given a value, or the insert fails.

UPDATE: change existing rows

UPDATE customers
SET city = 'Pune'
WHERE id = 8;

SET says what to change. WHERE says which rows. Hari had no city, and now has Pune. You can set several columns by separating them with commas, and use the old value in a calculation:

UPDATE products
SET price = price + 10
WHERE category = 'Stationery';

This raises the price of the three Stationery products by 10. The database tells you how many rows changed. If you expected 3 and it says 8, something is wrong, and you should stop and check.

DELETE: remove rows

DELETE FROM customers WHERE id = 9;

This removes Ivy. Again, WHERE decides which rows go. DELETE FROM customers; with no WHERE empties the whole table. In many companies, rows are not really deleted. Instead a column such as active or deleted_at is set, which is called a soft delete, so the data can be recovered and old reports still add up.

The three safety habits

1. SELECT first. Before an UPDATE or DELETE, run a SELECT with the same WHERE and look at the rows. If the list is what you expect, change SELECT * to DELETE and keep the same WHERE.
2. Check the row count. After the statement, read how many rows were affected. A surprise number means stop.
3. Use a transaction when the change matters. It lets you undo.

-- Step 1: look at what would change
SELECT * FROM products WHERE category = 'Stationery';

-- Step 2: change it
UPDATE products SET price = price + 10 WHERE category = 'Stationery';

Transactions: a safety net

A transaction groups statements into one all-or-nothing unit. You start with BEGIN, make changes, and finish with COMMIT to keep them or ROLLBACK to undo them all. Nobody else sees half-done work, and if something fails in the middle, you can go back.

BEGIN;
UPDATE products SET price = 0;      -- oops, forgot the WHERE
SELECT SUM(price) FROM products;    -- 0, that is wrong
ROLLBACK;                           -- undo everything
SELECT SUM(price) FROM products;    -- 5110 again

After ROLLBACK the prices are back. Real systems use transactions all the time, for example when moving money: take it from one account and add it to another. Both steps must happen, or neither. In MySQL and PostgreSQL the start is BEGIN or START TRANSACTION. Some tools auto-commit each statement unless you start a transaction yourself, so check your tool's setting.

What foreign keys do when you change data

Remember the foreign keys from lesson 8? When they are enforced, the database protects the links. In SQLite, turn them on first with PRAGMA foreign_keys = ON;.

PRAGMA foreign_keys = ON;

DELETE FROM customers WHERE id = 1;
-- Error: FOREIGN KEY constraint failed (Asha still has orders)

INSERT INTO orders (customer_id, order_date, status)
VALUES (99, '2024-09-01', 'pending');
-- Error: FOREIGN KEY constraint failed (there is no customer 99)

These errors are good news. The database is stopping you from creating orphan data. You can design a foreign key with ON DELETE CASCADE, which deletes the child rows automatically, or ON DELETE SET NULL. Use cascade carefully, because it can delete much more than you planned.

Other useful things

• INSERT ... SELECT copies rows from one table into another.
• UPDATE can use a subquery in SET or WHERE to decide the new value or rows.
• REPLACE or INSERT ... ON CONFLICT (an upsert) inserts a row, or updates it if it already exists. The syntax differs between engines.
• TRUNCATE TABLE empties a table quickly in MySQL, PostgreSQL and SQL Server. SQLite uses DELETE FROM table.

Common mistakes

• No WHERE on UPDATE or DELETE.
• A WHERE that matches more rows than you thought, for example city = 'Nairobi' also matching rows you did not mean to change.
• Putting text without quotes, or a number in quotes where a number is needed.
• Forgetting to COMMIT and then wondering why other people cannot see your change, or the opposite: committing by habit before checking.

Your task

1. Insert a new product called Eraser in the Stationery category with price 20, and select it back.
2. Raise the price of the Mouse by 50, but first run the matching SELECT.
3. Start a transaction, delete the order item with id 15, check the row count in order_items, then roll back and check again.
4. Turn on foreign keys and try to delete customer 1. Read the error message.

How to check it worked

• After the insert the products table has 9 rows.
• After the rollback the order_items table has 15 rows again.
• The foreign key error appears when you delete a customer who has orders.