SQL DML Commands Tutorial: INSERT, UPDATE & DELETE Data
📖 Introduction: The Power to Change Data
So far, you've learned to retrieve data with SELECT. Now it's time to learn how to create, modify, and remove data. These commands are called DML (Data Manipulation Language).
| Command | Action | Risk Level |
|---|---|---|
INSERT | Add new data | Low |
UPDATE | Change existing data | High |
DELETE | Remove existing data | Very High |
⚠️ Critical Warning:
UPDATEandDELETEwithout aWHEREclause affect every single row in the table. In production, this can destroy millions of records in seconds. Always useWHERE!
🛠️ Setting Up Practice Data
Let's create a customers table for our CRUD project:
1CREATE TABLE customers ( 2 customer_id INT PRIMARY KEY AUTO_INCREMENT, 3 first_name VARCHAR(50) NOT NULL, 4 last_name VARCHAR(50) NOT NULL, 5 email VARCHAR(100) UNIQUE, 6 phone VARCHAR(20), 7 city VARCHAR(50), 8 status VARCHAR(20) DEFAULT 'Active', 9 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 10);
1️⃣ INSERT INTO — Adding New Data
Purpose: Add one or more new rows to a table.
Syntax:
1INSERT INTO table_name (column1, column2, column3) 2VALUES (value1, value2, value3);
Example 1: Insert a Single Row
1INSERT INTO customers (first_name, last_name, email, phone, city) 2VALUES ('John', 'Doe', 'john.doe@email.com', '555-0101', 'New York');
What happens:
- A new row is created with the provided values
customer_idauto-increments (1, 2, 3...)statusdefaults to 'Active'created_atdefaults to current timestamp
Verify:
1SELECT * FROM customers;
Output:
+-------------+------------+-----------+-------------------+------------+-------------+--------+---------------------+
| customer_id | first_name | last_name | email | phone | city | status | created_at |
+-------------+------------+-----------+-------------------+------------+-------------+--------+---------------------+
| 1 | John | Doe | john.doe@email.com| 555-0101 | New York | Active | 2026-08-13 10:00:00 |
+-------------+------------+-----------+-------------------+------------+-------------+--------+---------------------+
Example 2: Insert Another Row
1INSERT INTO customers (first_name, last_name, email, phone, city) 2VALUES ('Jane', 'Smith', 'jane.smith@email.com', '555-0102', 'Los Angeles');
Example 3: Insert Multiple Rows (Batch Insert)
Purpose: Add several rows in a single command. Much faster than multiple individual inserts.
1INSERT INTO customers (first_name, last_name, email, phone, city) 2VALUES 3 ('Bob', 'Wilson', 'bob.w@email.com', '555-0103', 'Chicago'), 4 ('Alice', 'Brown', 'alice.b@email.com', '555-0104', 'New York'), 5 ('Charlie', 'Davis', 'charlie.d@email.com', '555-0105', 'Miami');
Verify:
1SELECT * FROM customers;
Output:
+-------------+------------+-----------+---------------------+------------+-------------+--------+---------------------+
| customer_id | first_name | last_name | email | phone | city | status | created_at |
+-------------+------------+-----------+---------------------+------------+-------------+--------+---------------------+
| 1 | John | Doe | john.doe@email.com | 555-0101 | New York | Active | 2026-08-13 10:00:00 |
| 2 | Jane | Smith | jane.smith@email.com| 555-0102 | Los Angeles | Active | 2026-08-13 10:01:00 |
| 3 | Bob | Wilson | bob.w@email.com | 555-0103 | Chicago | Active | 2026-08-13 10:02:00 |
| 4 | Alice | Brown | alice.b@email.com | 555-0104 | New York | Active | 2026-08-13 10:03:00 |
| 5 | Charlie | Davis | charlie.d@email.com | 555-0105 | Miami | Active | 2026-08-13 10:04:00 |
+-------------+------------+-----------+---------------------+------------+-------------+--------+---------------------+
Best Practice: Always list column names explicitly. Don't rely on column order. This makes your code more readable and prevents errors if the table structure changes.
Example 4: Insert with Default Values
If a column has a DEFAULT value, you can omit it:
1INSERT INTO customers (first_name, last_name, email) 2VALUES ('Eve', 'Johnson', 'eve.j@email.com');
Result: phone and city will be NULL, status will be 'Active', and created_at will be the current time.
2️⃣ UPDATE — Modifying Existing Data
Purpose: Change values in existing rows.
Syntax:
1UPDATE table_name 2SET column1 = value1, column2 = value2 3WHERE condition;
🚨 CRITICAL: The
WHEREclause determines which rows get updated. Without it, ALL rows are updated!
Example 5: Update a Single Column (One Row)
Scenario: John Doe moved to Boston.
1UPDATE customers 2SET city = 'Boston' 3WHERE customer_id = 1;
Verify:
1SELECT * FROM customers WHERE customer_id = 1;
Output:
+-------------+------------+-----------+-------------------+------------+--------+--------+---------------------+
| customer_id | first_name | last_name | email | phone | city | status | created_at |
+-------------+------------+-----------+-------------------+------------+--------+--------+---------------------+
| 1 | John | Doe | john.doe@email.com| 555-0101 | Boston | Active | 2026-08-13 10:00:00 |
+-------------+------------+-----------+-------------------+------------+--------+--------+---------------------+
Example 6: Update Multiple Columns
Scenario: Jane Smith got married, changed her name, and moved.
1UPDATE customers 2SET last_name = 'Williams', 3 city = 'San Francisco', 4 phone = '555-0199' 5WHERE customer_id = 2;
Example 7: Update Based on a Condition
Scenario: All customers in New York get a status upgrade to 'Premium'.
1UPDATE customers 2SET status = 'Premium' 3WHERE city = 'New York';
Before running UPDATE, always preview with SELECT:
1SELECT * FROM customers WHERE city = 'New York';
⚠️ The DANGER of UPDATE Without WHERE
1-- NEVER DO THIS IN PRODUCTION! 2UPDATE customers 3SET status = 'Inactive';
Result: Every single customer now has status = 'Inactive'. There is no undo button!
Safety Rule: Always write the
WHEREclause first, then fill in theSETclause.
3️⃣ DELETE FROM — Removing Data
Purpose: Permanently remove rows from a table.
Syntax:
1DELETE FROM table_name 2WHERE condition;
🚨 CRITICAL: Without
WHERE, you delete every row in the table!
Example 8: Delete a Single Row
Scenario: Customer Charlie Davis (ID 5) closed his account.
1DELETE FROM customers 2WHERE customer_id = 5;
Verify:
1SELECT * FROM customers WHERE customer_id = 5; 2-- Returns: Empty set
Example 9: Delete Multiple Rows by Condition
Scenario: Remove all customers with 'Inactive' status.
1-- Step 1: Preview what will be deleted 2SELECT * FROM customers WHERE status = 'Inactive'; 3 4-- Step 2: Delete only if the preview looks correct 5DELETE FROM customers 6WHERE status = 'Inactive';
Example 10: Safe Delete Practice
The Golden Rule for DELETE:
1-- Step 1: Write a SELECT with the same WHERE clause 2SELECT * FROM customers WHERE customer_id = 3; 3 4-- Step 2: If correct, change SELECT * to DELETE 5DELETE FROM customers WHERE customer_id = 3;
Pro Tip: Some SQL clients have "Safe Update Mode" which prevents
UPDATEorDELETEwithout aWHEREclause. Enable it!
4️⃣ TRUNCATE vs DELETE
| Command | Removes | Can Rollback? | Resets Auto-Increment? | Speed |
|---|---|---|---|---|
DELETE | Specific rows (or all with no WHERE) | Yes (in transaction) | No | Slower (logs each row) |
TRUNCATE | ALL rows | No | Yes | Very fast |
1-- Deletes all rows but keeps table structure 2TRUNCATE TABLE customers;
Warning:
TRUNCATEis instant and irreversible. Use with extreme caution!
📝 Complete DML Command Reference
| Command | Purpose | Risk |
|---|---|---|
INSERT INTO ... VALUES | Add one row | Low |
INSERT INTO ... VALUES (), (), () | Add multiple rows | Low |
UPDATE ... SET ... WHERE | Modify specific rows | Medium |
UPDATE ... SET ... (no WHERE) | Modify ALL rows | CRITICAL |
DELETE FROM ... WHERE | Remove specific rows | Medium |
DELETE FROM ... (no WHERE) | Remove ALL rows | CRITICAL |
TRUNCATE TABLE | Remove all rows instantly | CRITICAL |
🚀 Hands-On Project: Customer Management CRUD System
Project Goal
Build a complete CRUD (Create, Read, Update, Delete) workflow for a customer database.
Step 1: CREATE — Add New Customers
1INSERT INTO customers (first_name, last_name, email, phone, city) 2VALUES 3 ('Sarah', 'Connor', 'sarah.c@email.com', '555-0201', 'Detroit'), 4 ('Michael', 'Scott', 'michael.s@email.com', '555-0202', 'Scranton'), 5 ('Dwight', 'Schrute', 'dwight.s@email.com', '555-0203', 'Scranton');
Step 2: READ — Retrieve Customer Data
1-- View all customers 2SELECT * FROM customers; 3 4-- View active customers only 5SELECT * FROM customers WHERE status = 'Active'; 6 7-- View customers by city 8SELECT first_name, last_name, city FROM customers WHERE city = 'Scranton';
Step 3: UPDATE — Modify Customer Records
1-- Update phone number for Sarah Connor 2UPDATE customers 3SET phone = '555-0299' 4WHERE customer_id = 6; 5 6-- Upgrade all Scranton customers to Premium 7UPDATE customers 8SET status = 'Premium' 9WHERE city = 'Scranton'; 10 11-- Move a customer to a new city 12UPDATE customers 13SET city = 'Philadelphia', 14 status = 'Active' 15WHERE first_name = 'Michael' AND last_name = 'Scott';
Step 4: DELETE — Remove Customer Records
1-- Remove a specific customer 2DELETE FROM customers 3WHERE customer_id = 7; 4 5-- Remove all customers who never provided a phone number 6SELECT * FROM customers WHERE phone IS NULL; 7-- If correct: 8DELETE FROM customers WHERE phone IS NULL;
Step 5: Full CRUD Verification Script
1-- CREATE 2INSERT INTO customers (first_name, last_name, email, city) 3VALUES ('Test', 'User', 'test@email.com', 'Dallas'); 4 5-- READ 6SELECT * FROM customers WHERE email = 'test@email.com'; 7 8-- UPDATE 9UPDATE customers SET city = 'Houston' WHERE email = 'test@email.com'; 10 11-- READ again to verify 12SELECT * FROM customers WHERE email = 'test@email.com'; 13 14-- DELETE 15DELETE FROM customers WHERE email = 'test@email.com'; 16 17-- Final verification 18SELECT * FROM customers WHERE email = 'test@email.com'; 19-- Should return: Empty set
✅ Module 5 Summary
| Concept | Command | Key Rule |
|---|---|---|
| Add one row | INSERT INTO ... VALUES (...) | List columns explicitly |
| Add many rows | INSERT INTO ... VALUES (...), (...) | More efficient than multiple inserts |
| Change data | UPDATE ... SET ... WHERE ... | Never skip WHERE! |
| Remove data | DELETE FROM ... WHERE ... | Always preview with SELECT first! |
| Remove all data | TRUNCATE TABLE ... | Fast but irreversible |
🎯 Practice Exercises
- Insert a new customer with your own name and details.
- Insert three new customers in a single statement.
- Update the phone number of customer with
customer_id = 4. - Update all customers in 'New York' to status 'Premium'.
- Delete the customer with
customer_id = 3(after previewing with SELECT). - Try to delete all customers with
status = 'Inactive'(use SELECT first!). - Explain why
UPDATE customers SET status = 'Inactive';is dangerous.
🎓 What's Next?
In Module 6, you'll learn DDL (Data Definition Language) — how to create, alter, and drop tables and databases using CREATE TABLE, ALTER TABLE, DROP TABLE, and TRUNCATE. You'll design complete database schemas from scratch! 🚀