FOUNDATIONBEGINNERlesson-04

INSERT, UPDATE & DELETE — Modifying Data

🕑 25 min

📃 5 sections

🎓 Foundation

SELECT reads data, but INSERT, UPDATE, and DELETE change it. These three commands are called DML — Data Manipulation Language. One of the most important things a SQL developer learns is that UPDATE and DELETE without a WHERE clause can destroy your entire table. This lesson covers safe DML practices.

Section 1

INSERT INTO

SQL

-- Insert single row
INSERT INTO students (name, age, grade)
VALUES ('Zara Malik', 17, 'A');

-- Insert multiple rows (more efficient — one transaction)
INSERT INTO students (name, age, grade) VALUES
  ('Hassan Ali',   16, 'B'),
  ('Nadia Khan',   18, 'A+'),
  ('Bilal Ahmed',  15, 'B+');

-- INSERT from another table
INSERT INTO archive_students (name, age, grade)
SELECT name, age, grade FROM students WHERE created_at < '2025-01-01';

Section 2

UPDATE — Always Use WHERE

SQL

-- ALWAYS use WHERE with UPDATE!
UPDATE students
SET grade = 'A+'
WHERE id = 3;

-- Update multiple columns
UPDATE students
SET grade = 'A', email = 'newemail@test.com'
WHERE name = 'Hassan Ali';

-- Update with calculation
UPDATE products
SET price = price * 1.10    -- 10% price increase
WHERE category = 'Electronics';

⚠️

Critical Warning: Always Use WHERE

Running UPDATE without WHERE updates EVERY row. Before a mass update, always test with SELECT first using the same WHERE condition.

Section 3

DELETE

SQL

-- Delete a specific row (WHERE is critical!)
DELETE FROM students WHERE id = 5;

-- Delete multiple rows
DELETE FROM students WHERE grade = 'F';

-- TRUNCATE removes ALL rows (faster, resets AUTO_INCREMENT)
TRUNCATE TABLE temp_logs;

-- DELETE vs TRUNCATE:
-- DELETE: can use WHERE, can be rolled back, slow on large tables
-- TRUNCATE: no WHERE, faster, resets auto-increment

Section 4

Safe DML with Transactions

SQL

-- Safe update pattern
START TRANSACTION;

UPDATE students SET grade = 'A+' WHERE id = 10;

-- Check the result before committing
SELECT * FROM students WHERE id = 10;

-- If result looks correct:
COMMIT;

-- If something is wrong:
-- ROLLBACK;

Section 5

ON DUPLICATE KEY UPDATE (Upsert)

SQL

-- Insert or update if duplicate key exists
INSERT INTO students (id, name, grade)
VALUES (1, 'Sara Ahmed', 'A+')
ON DUPLICATE KEY UPDATE grade = 'A+';

-- REPLACE INTO (delete + re-insert)
REPLACE INTO students (id, name, grade)
VALUES (1, 'Sara Ahmed', 'A+');

DML Decision Flow

Does row exist?

→ YES →

UPDATE

→

COMMIT

Does row exist?

→ NO →

INSERT

→

COMMIT

Any error?

→ YES →

ROLLBACK

🎤 Knowledge Check

1. What happens if UPDATE has no WHERE clause?

Every single row in the table gets updated — this is one of the most dangerous mistakes in SQL. Always check your WHERE clause first!

2. Difference between DELETE and TRUNCATE?

DELETE is flexible (supports WHERE, can be rolled back) but slower. TRUNCATE is all-or-nothing but much faster on large tables and resets AUTO_INCREMENT.

3. What does ROLLBACK do?

ROLLBACK is your undo button — it reverses all changes made since the last COMMIT or START TRANSACTION. It's a safety net for accidental updates.

4. INSERT ... SELECT does what?

INSERT ... SELECT copies rows from one query result into a destination table — perfect for archiving, data migration, or creating summary tables.

5. Which is faster for removing ALL rows from a large table?

TRUNCATE is far faster than DELETE for clearing all rows — it deallocates data pages directly rather than deleting row by row.