INSERT, UPDATE and DELETE

INSERT adds new rows, UPDATE changes values in existing rows, and DELETE removes rows. UPDATE and DELETE act on every row their WHERE matches, and on every row of the table if there is no WHERE.

Why it matters

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.

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.

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.

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.

Syntax

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

How it works

  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.

Key points

Common mistakes

Not true: An UPDATE with no WHERE changes only the first row of the table.
In fact: With no WHERE, UPDATE changes every row. Run the WHERE as a SELECT first, and check how many rows it hits.

In the interview

INSERT adds rows, ideally with an explicit column list; UPDATE changes columns in rows matched by WHERE, and DELETE removes matched rows. I have three safety habits. I run the WHERE as a SELECT first. I wrap risky changes in a transaction, so I can roll back. And when a row may or may not exist, I use an upsert (insert, on conflict do update): one atomic statement instead of a check-then-write race.

Part of Unit 6: Building tables and changing data in the free SQL course.