UPDATE & DELETE

Change and remove rows — and why WHERE is not optional

Beginner 14 minDMLUPDATEDELETE

Changing and Removing Data

UPDATE modifies existing rows; DELETE removes them. Both are dangerous when used without a WHERE clause — a single missing keystroke can wipe a table. Here's how to use them safely:

UPDATE — UPDATE t SET col = value WHERE condition. The SET clause lists columns to change and their new values. The WHERE clause picks which rows are affected. Leave out the WHERE, and *every row* in the table gets the new value.

Computed updates — The new value can reference the current value: UPDATE products SET price = price * 1.10 WHERE category = 'Electronics' raises electronics prices by 10%. UPDATE orders SET status = 'archived', archived_at = DATE('now') WHERE order_date < '2023-01-01' updates multiple columns at once.

DELETE — DELETE FROM t WHERE condition. Same structure, same warning: DELETE FROM orders; with no WHERE empties the entire table silently. Run SELECT COUNT(*) FROM t WHERE condition first to see exactly how many rows will be removed.

The dry-run discipline — Before every UPDATE or DELETE in production, write it first as a SELECT with the same WHERE clause: SELECT * FROM orders WHERE status = 'pending' AND order_date < '2023-01-01'. If that returns 50,000 rows, you know what your DELETE is about to do. Professionals never skip this step.

Transactions — BEGIN ... COMMIT groups multiple changes into an atomic unit: if anything fails, ROLLBACK reverts everything. Essential when a single logical change spans multiple statements ("move row from active to archive" = DELETE + INSERT, and you want both or neither).

TRUNCATE — In Postgres, MySQL, SQL Server, and Oracle, TRUNCATE TABLE t empties a table faster than DELETE FROM t because it doesn't log each row. But it's a DDL operation — it can't be rolled back in MySQL, bypasses DELETE triggers, and resets auto-increment counters. SQLite doesn't have TRUNCATE; use DELETE FROM t instead.

Knowledge Canvas

How UPDATE & DELETE Work

Change and remove rows

UPDATE t SET col = value WHERE condition — change matching rows
DELETE FROM t WHERE condition — remove matching rows
Without WHERE, the statement affects EVERY row
Both are immediately effective — there is no undo button

Catastrophic Traps

The most dangerous SQL

UPDATE without WHERE → modifies every row silently
DELETE without WHERE → empties the table silently
DELETE with CASCADE FKs → may delete from other tables too
Forgetting COMMIT in a transaction → changes invisible / rolled back

The Dry-Run Habit

What professionals always do first

Run your WHERE clause as a SELECT first — see exactly what will change
Check COUNT(*) to confirm the row count matches expectations
Wrap risky changes in BEGIN ... COMMIT / ROLLBACK
Consider soft-delete: UPDATE status = 'deleted' instead of DELETE

UPDATE vs DELETE vs TRUNCATE

When to use which

UPDATE: change values

DELETE: remove rows

DELETE: logged, rollbackable

TRUNCATE: fast, not in SQLite

DELETE keeps auto-increment

TRUNCATE resets counters (most DBs)

Syntax

Syntax Template

1-- UPDATE (always include WHERE)

2UPDATE t

3SET col1 = value1,

4 col2 = value2

5WHERE condition;

7-- UPDATE with computed expression

8UPDATE products

9SET price = ROUND(price * 1.10, 2)

10WHERE category = 'Electronics';

12-- DELETE (always include WHERE)

13DELETE FROM t

14WHERE condition;

16-- Transaction for safety

17BEGIN;

18 UPDATE ...;

19 DELETE ...;

20COMMIT; -- or ROLLBACK; to undo

UPDATE ... SET ... WHEREChange values in existing rows — WHERE picks which

DELETE FROM ... WHERERemove rows — WHERE picks which

SET col = col * 1.10New value can reference the current value

BEGIN ... COMMITAtomic unit — all changes apply together, or ROLLBACK undoes

Worked Example

Set up the staff table, give every Engineer a 10% raise, then remove anyone earning under 80,000.

SQL

1CREATE TABLE IF NOT EXISTS staff (id INTEGER PRIMARY KEY, name TEXT, role TEXT, salary REAL);

2DELETE FROM staff; -- start fresh if re-running

3INSERT INTO staff (id, name, role, salary) VALUES

4 (1, 'Aarav', 'Engineer', 90000),

5 (2, 'Sara', 'Designer', 78000),

6 (3, 'James', 'Engineer', 95000),

7 (4, 'Priya', 'Manager', 120000);

9-- Dry run first: which rows will the UPDATE affect?

10SELECT id, name, salary FROM staff WHERE role = 'Engineer';

12-- Apply the raise

13UPDATE staff

14SET salary = ROUND(salary * 1.10, 2)

15WHERE role = 'Engineer';

17-- Dry run for the DELETE

18SELECT id, name, salary FROM staff WHERE salary < 80000;

20-- Apply the DELETE

21DELETE FROM staff WHERE salary < 80000;

23-- Verify final state

24SELECT * FROM staff ORDER BY id;

The two SELECT-first dry runs show exactly which rows each statement will touch. After the UPDATE, Aarav goes from 90000 → 99000 and James from 95000 → 104500. After the DELETE, Sara (78000) is removed while Priya (120000) remains. Run each statement separately in the Practice Lab to observe the step-by-step behavior.

Output

3 rows

id	name	role	salary
1	Aarav	Engineer	99000
3	James	Engineer	104500
4	Priya	Manager	120000

Common Mistakes

❌

UPDATE without WHERE

SQL

1UPDATE orders SET status = 'cancelled';

Sets status to 'cancelled' on *every* row in the table. No error, no warning. This is the most common SQL disaster.

Always write the WHERE first: UPDATE orders SET status = 'cancelled' WHERE id = 5001;

❌

DELETE without WHERE

SQL

1DELETE FROM customers;

Empties the entire customers table. Even worse: if you have FK constraints with CASCADE, this can cascade-delete orders, order_items, and reviews too.

DELETE FROM customers WHERE id = 'C1001'; — or better, run SELECT with the same WHERE first to see what you're about to delete.

❌

Forgetting transactions for multi-step changes

Moving a row from active to archive involves INSERT + DELETE. If the server crashes between the two, you either lose the row or have it in both places.

Wrap them in BEGIN ... COMMIT so both succeed together or neither does.

Key Concepts

1UPDATE or DELETE without WHERE affects every row — always test the WHERE as a SELECT first

2UPDATE can use computed expressions: SET price = price * 1.10

3BEGIN ... COMMIT wraps multi-statement changes atomically; ROLLBACK undoes them

4DELETE removes rows; TRUNCATE (not in SQLite) is faster but cannot be rolled back

Pro Tip

UPDATE and DELETE are how data evolves over time. They're also where most catastrophic data losses begin — a forgotten WHERE clause can destroy months of work in milliseconds.

When to Use

Correcting data quality bugs, applying price changes, archiving old records, anonymizing user data, soft-deleting accounts (UPDATE status = 'deleted'), cleaning up test rows.

INSERT Statement

ALTER TABLE & DROP TABLE