UPDATE & DELETE

UPDATE modifies rows; DELETE removes rows. Always use WHERE!

TRUNCATE

Removes all rows faster than DELETE. Resets auto-increment.

Transactions

Use START TRANSACTION, COMMIT, ROLLBACK for atomicity.

Syntax

MYSQL

1
2
3
4

UPDATE users SET email = "new@mail.com" WHERE id = 1;
UPDATE products SET price = price * 1.10 WHERE category = "Electronics";
DELETE FROM users WHERE status = "inactive";
TRUNCATE TABLE logs;

UPDATE and DELETE

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Update single row
UPDATE users
SET city = "Multan"
WHERE id = 1;

-- Update multiple columns
UPDATE users
SET name = "Alice Khan", city = "Lahore"
WHERE email = "alice@example.com";

-- Update with condition
UPDATE users
SET status = "active"
WHERE age >= 18;

-- Delete specific row
DELETE FROM users
WHERE id = 5;

-- Delete older than 2024
DELETE FROM users
WHERE created_at < "2024-01-01";

-- Verify no orphans
SELECT COUNT(*) FROM users;

Try It Yourself

Practice

1

Exercise

50% off all Clearance products.

Answer

UPDATE products SET price = price * 0.50 WHERE category = "Clearance";

Quick Quiz

1

UPDATE without WHERE?

Error

First row only

All rows

No rows

All rows are updated.

Retry Quiz

Interview Questions

Why transactions for multi-table updates?

Ensures atomicity � all changes succeed or none do, preventing inconsistent states.

Previous

INSERT INTO

Tutorial Complete

Back to MySQL