Lesson 3 of 5 · 15–25 minutes

INSERT, UPDATE and DELETE

🏠The everyday picture

Adding a new line to the register, correcting a line, and striking a line out. The correction and the striking-out must be done on exactly the right line.

📘What it really is

Insert rows (list the columns, so the statement keeps working if columns are added later):

INSERT INTO customers (name, phone, city)
VALUES ('Asha Kumari', '+919800000001', 'Patna'),
       ('Ravi Singh', '+919800000002', 'Gaya');

Update rows:

UPDATE customers
SET city = 'Muzaffarpur'
WHERE id = 7;

Delete rows:

DELETE FROM customers WHERE id = 7;

The most dangerous mistake in SQL: forgetting WHERE. UPDATE customers SET city = 'Patna'; changes every customer. DELETE FROM customers; deletes everyone.
Safe habits:
write the SELECT with the same WHERE first, and check it finds exactly the rows you mean;
then change SELECT * to UPDATE ... SET or DELETE;
for anything important, do it inside a transaction you can roll back (Module 57), and have a backup.
RETURNING (PostgreSQL and SQLite) shows the rows that were inserted, changed or deleted: INSERT INTO ... RETURNING id;.

🖱️Try it

Insert five courses in one statement. Update one course's fee, using a SELECT with the same WHERE first. Delete one course you inserted by mistake. Then, in a copy of your database, run an UPDATE without WHERE to see the damage.

✅Quick check

What does DELETE FROM customers; do?

Show the answer

Deletes every row in the table.

What's the safest way to write an UPDATE?

Show the answer

Run a SELECT with the same WHERE first to check exactly which rows will change.