How do INSERT, UPDATE and DELETE work?

Question

IT Interview Practice · Accepted Answer

These are the **DML** (Data Manipulation Language) commands that *change* data: **`INSERT`** adds rows, **`UPDATE`** modifies existing rows, **`DELETE`** removes rows. Unlike `SELECT` (which only reads), these modify the database — so they require care, especially the `WHERE` clause.

## INSERT — add rows

```sql
-- insert one row
INSERT INTO users (name, email, age)
VALUES ('Ann', 'ann@x.com', 30);

-- insert multiple rows at once (efficient)
INSERT INTO users (name, email)
VALUES ('Bob', 'bob@x.com'), ('Carol', 'carol@x.com');

-- insert from a query
INSERT INTO archived_users SELECT * FROM users WHERE inactive = true;
```

You specify the columns and values. Inserting multiple rows in one statement is more efficient than many separate inserts.

## UPDATE — modify rows (WHERE is critical!)

```sql
UPDATE users
SET age = 31, updated_at = NOW()      -- set one or more columns
WHERE id = 5;                          -- ❗ WHICH rows to update
```

```sql
-- ❌ DANGER — no WHERE clause updates EVERY row in the table!
UPDATE users SET active = false;       -- sets ALL users inactive 😱
```

**Critical:** an `UPDATE` (or `DELETE`) **without a `WHERE` clause affects every row in the table.** Forgetting `WHERE` is a catastrophic, common mistake — always double-check it.

## DELETE — remove rows

```sql
DELETE FROM users WHERE id = 5;        -- delete a specific row
DELETE FROM users WHERE inactive = true;  -- delete matching rows

-- ❌ DANGER — no WHERE deletes the ENTIRE table's contents!
DELETE FROM users;
```

## Safety practices

```text
✓ ALWAYS include a WHERE clause (unless you truly mean to affect all rows)
✓ Test the WHERE first with SELECT (SELECT * WHERE ... → see what you'd affect)
✓ Use TRANSACTIONS (BEGIN ... COMMIT/ROLLBACK) so you can undo mistakes
✓ DELETE vs TRUNCATE: TRUNCATE quickly empties a whole table (faster, but not row-by-row,
  can't be rolled back in some DBs, resets auto-increment)
```

## Why it matters

`INSERT`, `UPDATE`, and `DELETE` are how applications **change data** — fundamental to any application that stores and modifies information (creating records, editing, removing), so understanding them is essential everyday knowledge alongside `SELECT`.

Knowing how to insert (single, multiple, or from a query), update (setting columns), and delete rows is necessary for all data-modifying operations.

The **most critical point is the danger of a missing `WHERE` clause**: an `UPDATE` or `DELETE` without `WHERE` affects *every row* in the table — a catastrophic, surprisingly common mistake that can wipe out or corrupt all data.

Understanding this danger and the **safety practices** (always include WHERE, test the condition with a SELECT first, use transactions so mistakes can be rolled back) is vital knowledge that prevents serious data-loss accidents.

Knowing related details (efficient multi-row inserts, DELETE vs TRUNCATE for emptying tables) rounds out the practical knowledge.

Since modifying data is fundamental to applications, and since the missing-WHERE mistake is both catastrophic and common (a frequent cause of real production data disasters), mastering these DML commands — and especially the critical discipline around the WHERE clause and transactional safety — is core, must-know knowledge for anyone who writes data-changing SQL, where the stakes (irreversible data loss) make the safety practices as important as the syntax.

INSERT — add rows

UPDATE — modify rows (WHERE is critical!)

DELETE — remove rows

Safety practices

Why it matters

Related questions