Lesson 10 • Intermediate
INSERT, UPDATE, DELETE
Add, modify, and remove data safely — the write operations every developer must master.
🎯 What You'll Learn
- INSERT single and multiple rows
- INSERT from SELECT (copy data between tables)
- UPDATE with conditions, calculations, and subqueries
- DELETE safely and the difference vs. TRUNCATE
- Safe modification patterns with transactions
INSERT — Adding Data
INSERT INTO adds new rows to a table. You can insert one row, many rows, or copy rows from another table.
📦 Real-World Analogy
INSERT is like filling out a new form and adding it to a filing cabinet. You specify which fields to fill (name, email) and what values to put in.
💡 Pro Tip
Always specify column names in INSERT: INSERT INTO t (col1, col2). Omitting them makes your code break when columns are added or reordered.
INSERT Operations
Add single rows, multiple rows, and insert from queries
-- Insert a single row
INSERT INTO customers (name, email, phone)
VALUES ('Alice Johnson', 'alice@example.com', '555-1234');
-- Insert multiple rows at once
INSERT INTO products (name, price, category, stock)
VALUES
('Laptop Pro', 1299.99, 'Electronics', 50),
('Wireless Mouse', 29.99, 'Electronics', 200),
('Standing Desk', 499.99, 'Furniture', 30);
-- Insert from another query
INSERT INTO archived_orders
SELECT * FROM orders
WHERE order_date < '2023-01-01';UPDATE — Modifying Data
UPDATE changes existing values in rows that match a WHERE condition.
⚠️ Critical Warning
Always include a WHERE clause! Running UPDATE employees SET salary = 0 without WHERE sets everyone's salary to zero. This is the most dangerous SQL mistake beginners make.
UPDATE Operations
Modify single values, use calculations, and subquery-driven updates
-- Update a single column
UPDATE products
SET price = 899.99
WHERE product_id = 42;
-- Update multiple columns
UPDATE customers
SET email = 'new@example.com',
phone = '555-9999',
updated_at = CURRENT_TIMESTAMP
WHERE customer_id = 100;
-- Update with calculation: 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering'
AND performance_rating >= 4;
-- Update with subquery
UPDATE products
SET price = price * 0.85
WHERE category_id IN (
SELECT id FROM c
...DELETE vs TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| WHERE clause | ✅ Yes | ❌ No |
| Rollback | ✅ Yes | ❌ Usually not |
| Auto-increment | Keeps current | Resets to 1 |
| Triggers | ✅ Fires | ❌ Doesn't fire |
| Speed | Slower | Much faster |
DELETE Operations
Remove rows safely and understand DELETE vs TRUNCATE
-- Delete specific rows
DELETE FROM orders
WHERE status = 'cancelled'
AND order_date < '2023-01-01';
-- Delete with subquery
DELETE FROM products
WHERE product_id NOT IN (
SELECT DISTINCT product_id FROM order_items
);
-- Delete all rows (keeps table structure)
DELETE FROM temp_logs;
-- TRUNCATE: faster delete all (resets auto-increment)
TRUNCATE TABLE temp_logs;
-- IMPORTANT: always test with SELECT first!
-- Step 1: Check what will be deleted
SELECT * FROM orders
WHERE status = 'canc
...The Safe Modification Workflow
Professional developers follow this pattern for every data modification:
- SELECT first — run your WHERE clause with SELECT to preview affected rows
- BEGIN TRANSACTION — wrap changes in a transaction
- Run the modification — INSERT, UPDATE, or DELETE
- Verify results — SELECT again to check the changes
- COMMIT or ROLLBACK — save if correct, undo if wrong
Safe Modification with Transactions
The professional workflow: wrap changes in transactions for safety
-- Safe modification pattern with transactions
BEGIN TRANSACTION;
-- Transfer $500 between accounts
UPDATE accounts SET balance = balance - 500
WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 500
WHERE account_id = 1002;
-- Verify the changes
SELECT account_id, balance FROM accounts
WHERE account_id IN (1001, 1002);
-- If everything looks correct:
COMMIT;
-- If something went wrong:
-- ROLLBACK;📘 Quick Reference
| Statement | Purpose |
|---|---|
| INSERT INTO t (cols) VALUES (...) | Add new rows |
| INSERT INTO t SELECT ... FROM t2 | Copy rows from another table |
| UPDATE t SET col=val WHERE ... | Modify existing rows |
| DELETE FROM t WHERE ... | Remove specific rows |
| TRUNCATE TABLE t | Remove all rows (fast reset) |
🎉 Lesson Complete!
You can now safely add, modify, and remove data. Next, you'll learn about indexes — the key to making your queries lightning fast!
Sign up for free to track which lessons you've completed and get learning reminders.