INSERT, UPDATE and DELETE

Step 1 Understand it

Why you need it. A database is only useful if it can be kept up to date: new customers, price changes, cancelled orders. These three statements do all of that — and one missing WHERE can change or wipe every row, so they deserve care.

Watch it happen

Picture it

flowchart LR
  T["a table"] --> I["INSERT<br/>adds rows"]
  T --> U["UPDATE … WHERE<br/>changes matching rows"]
  T --> D["DELETE … WHERE<br/>removes matching rows"]
  U -.->|"no WHERE"| ALL["every row changed"]
  D -.->|"no WHERE"| ALL2["every row removed"]

The idea

INSERT INTO table (columns) VALUES (…) adds a row; list the columns so the order is clear. INSERT … SELECT copies rows from a query. UPDATE table SET column = value WHERE condition changes matching rows, and the new value can use the old one (salary = salary + 5000). DELETE FROM table WHERE condition removes matching rows. An upsert — INSERT … ON CONFLICT DO UPDATE — inserts a row, or updates it if the key already exists.

The shape of it

INSERT INTO t (col1, col2) VALUES (v1, v2);
UPDATE t SET col1 = new_value WHERE condition;
DELETE FROM t WHERE condition;

Words in lower case are the parts you fill in.

How it works, step by step

1. INSERT adds Ravi as customer 7. The constraints are checked first.
2. UPDATE employees SET salary = salary + 5000 WHERE dept_id = 2: Dara and Eli get the raise, nobody else.
3. DELETE FROM customers WHERE customer_id = 6 removes Tom's duplicate sign-up (he has no orders, so the foreign key allows it).
4. Upsert: inserting product 7 again hits the primary key, so DO UPDATE changes its price instead.
5. Safe habit: write SELECT * FROM t WHERE … first, check the rows, then turn it into the UPDATE or DELETE.

Analogy

Editing a spreadsheet. INSERT adds a new line at the bottom. UPDATE is find-and-replace — and WHERE is the "only in this selection" box. DELETE removes lines. Forgetting WHERE is "replace all" across the whole sheet.

Where the analogy breaks

A spreadsheet has Undo. The database doesn't: once a change is committed, it is permanent. That is why you run the WHERE as a SELECT first, and use transactions (next lesson) so you can roll back.

Worked examples

INSERT INTO customers (customer_id, name, email, city, signup_date)
VALUES (7, 'Ravi', 'ravi@mail.com', 'Leeds', '2024-03-25');
SELECT customer_id, name, city
FROM customers
WHERE city = 'Leeds'
ORDER BY customer_id;

UPDATE employees
SET salary = salary + 5000
WHERE dept_id = 2;
SELECT name, salary
FROM employees
WHERE dept_id = 2
ORDER BY name;

DELETE FROM customers
WHERE customer_id = 6;
SELECT customer_id, name
FROM customers
ORDER BY customer_id;

INSERT INTO products (product_id, product_name, category, price)
VALUES (7, 'Phone case', 'Electronics', 12)
ON CONFLICT (product_id) DO UPDATE SET price = excluded.price;
SELECT product_id, product_name, price
FROM products
WHERE category = 'Electronics'
ORDER BY product_id;

Must-know points

• Always list the columns in INSERT; don't rely on the table's column order.
• UPDATE and DELETE without WHERE change every row.
• Run the WHERE as a SELECT first, and check how many rows it hits.
• INSERT INTO t (…) SELECT … copies rows from a query.
• An upsert inserts or updates in one atomic statement.

Common mistakes

• Forgetting WHERE on an UPDATE or DELETE.
• Deleting parent rows that child rows still reference — the foreign key refuses, or cascades if set to.
• Updating with a value computed from another table without checking the join returns one row per target row.

In other databases

• PostgreSQL and SQLite support RETURNING to see the rows you changed: DELETE FROM t WHERE … RETURNING *. SQL Server uses OUTPUT.
• UPDATE with a join: PostgreSQL UPDATE t SET … FROM other WHERE …; MySQL UPDATE t JOIN other ON … SET …; SQL Server UPDATE t SET … FROM t JOIN other ….

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 3: Writing

  Harvard, free

• Read

  W3Schools — SQL UPDATE Statement

  short page with a try-it editor, free

• Practice

  SQLBolt — Lesson 13: Inserting rows

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why you should run the WHERE as a SELECT before an UPDATE or DELETE.

   Show answer

   UPDATE and DELETE act on every row their WHERE matches, and there is no undo once it is committed. A WHERE that is slightly wrong (a typo, OR where you meant AND, a NULL you forgot) can change far more rows than you meant, or none at all.

   Running the same WHERE as a SELECT first shows you exactly which rows will be hit and how many. If you expected 2 rows and see 2,000, you have just avoided a disaster. Then turn the SELECT into the UPDATE or DELETE, ideally inside a transaction, and check that the number of rows changed matches.

2. Writefrom a blank page, then run it

   Give everyone in Engineering a 10% raise (whole pounds), then show their names and new salaries.

   Show answer

   Multiply by 110 and divide by 100. With whole numbers, the division drops any pence, which is what "whole pounds" asks for. The department is found by its name, so the query doesn't depend on remembering that Engineering is 1.

   sqlCopy

   UPDATE employees
   SET salary = salary * 110 / 100
   WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Engineering');
   SELECT name, salary
   FROM employees
   WHERE dept_id = 1
   ORDER BY name;

   Try it

   4 rows

   name	salary
   Asha	104500
   Ben	77000
   Chen	77000
   Hana	88000

3. Predictsay it before you run it

   How many rows does DELETE FROM order_items WHERE quantity >= 3; remove?

   Show answer

   7 rows: the item lines with a quantity of 3 or more. They are (101, headphones, 3), (103, mug, 5), (104, headphones, 5), (105, headphones, 3), (108, desk lamp, 3), (109, pen, 5) and (109, notebook, 4). 7 of the 14 remain.

   In SQLite, changes() reports how many rows the last statement changed.

   sqlCopy

   DELETE FROM order_items WHERE quantity >= 3;
   SELECT changes() AS rows_removed, (SELECT COUNT(*) FROM order_items) AS rows_left;

   Try it

   1 row

   rows_removed	rows_left
   7	7

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Try DELETE FROM customers WHERE customer_id = 1;. What happens and why? What would ON DELETE CASCADE have done?

   Show answer

   It fails: "FOREIGN KEY constraint failed". Priya (customer 1) has five orders, and each one's customer_id points at her. Deleting her would leave those orders pointing at nobody, so the database refuses, and nothing is deleted.

   With ON DELETE CASCADE on orders.customer_id, deleting Priya would also delete her five orders automatically. Those orders have 9 item lines in order_items, so that foreign key would need CASCADE too; otherwise it would still block the delete. One innocent-looking DELETE would remove 15 rows across three tables, which is why CASCADE should be a deliberate choice.

   sqlCopy

   DELETE FROM customers WHERE customer_id = 1;

   Try it

   Error: FOREIGN KEY constraint failed

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

INSERT adds rows, ideally with an explicit column list; UPDATE changes columns in rows matched by WHERE, and DELETE removes matched rows. My safety habits are to run the WHERE as a SELECT first, wrap risky changes in a transaction so I can roll back, and use an upsert — insert on conflict do update — when a row may or may not exist, so it is a single atomic statement rather than a check-then-write race.

Follow-up questions

Tick each check when you can do it without notes