MySQL: A Detailed Explanation of MySQL Insert, Delete, and…

Last updated: 2026-08-26

Inserting, updating, and deleting data are basic database operations—INSERT, UPDATE, and DELETE are the core of DML.

This lesson provides a systematic explanation of how to insert, update, and delete data.

graph TB
    A[DML Operation] --> B[INSERT Insert]
    A --> C[UPDATE Update]
    A --> D[DELETE Delete]
    B --> B1[Single-row insertion]
    B --> B2[Multi-line Insertion]
    B --> B3[Subquery Insertion]
    B --> B4[UPSERT]
    C --> C1[Conditional Update]
    C --> C2[JOIN Update]
    D --> D1[Conditional Deletion]
    D --> D2[TRUNCATE Clear]

graph TB
    A[DML Operation] --> B[INSERT Insert]
    A --> C[UPDATE Update]
    A --> D[DELETE Delete]
    B --> B1[Single-row insertion]
    B --> B2[Multi-line Insertion]
    B --> B3[Subquery Insertion]
    B --> B4[UPSERT]
    C --> C1[Conditional Update]
    C --> C2[JOIN Update]
    D --> D1[Conditional Deletion]
    D --> D2[TRUNCATE Clear]

1. What You'll Learn

• INSERT: Single-row, multi-row, and subquery inserts
• UPDATE: Updating Conditions
• DELETE with conditions
• TRUNCATE: Empty the table
• INSERT ON DUPLICATE KEY UPDATE

2. A True Story

(1) Pain Point: Manually entering data one by one is extremely inefficient

The inventory management system processes over 10,000 data writes daily. Operations staff manually perform INSERT and UPDATE operations one by one, which is not only extremely inefficient but also frequently leads to data corruption due to copy-and-paste errors. During a batch price adjustment, executing UPDATE statements one by one took three hours; during that time, a staff member accidentally entered the wrong WHERE clause, causing all prices in the entire table to be set to 0.

(2) A Solution Combining Batch Operations and Transactions

Use batch INSERTs, transactional UPDATEs, and conditional DELETEs, combined with INSERT ON DUPLICATE KEY UPDATE, to process all data in a single operation.

3. INSERT: Inserting Data

▶ Example: Single-line insertion

-- Insert in Full
INSERT INTO users (username, email, age) 
VALUES ('alice', 'alice@email.com', 25);

-- Omit field names（Must be in order）
INSERT INTO users VALUES (1, 'bob', 'bob@email.com', 30);

Output:

Query OK, 1 row affected

▶ Example: Inserting Multiple Lines

INSERT INTO products (name, price, category) VALUES
('iPhone', 999, 'Phone'),
('MacBook', 1999, 'Laptop'),
('iPad', 599, 'Tablet');

Output:

Query OK, 1 row affected

▶ Example: Inserting a subquery

-- Insert from Query Results
INSERT INTO user_archive (id, name, email)
SELECT id, name, email FROM users WHERE status = 'deleted';

Output:

Output displayed

4. UPDATE: Update Data

▶ Example: Conditional Update

-- Update a Single Record
UPDATE users SET email = 'new@email.com' WHERE id = 1;

-- Update Multiple Records
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

-- Multi-Field Update
UPDATE users SET age = age + 1, status = 'active' WHERE id = 1;

Output:

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

▶ Example: UPDATE + JOIN

-- Related Updates
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.id
SET o.status = 'priority'
WHERE c.level = 'VIP';

Output:

Output displayed

5. DELETE: Deleting Data

▶ Example: Conditional Deletion

-- Delete a Single Record
DELETE FROM users WHERE id = 1;

-- Delete Multiple Records
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2025-01-01';

-- Deleting Related Records
DELETE o FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'banned';

Output:

Output displayed

(1) TRUNCATE vs DELETE

-- Clear the table (Non-rollbackable)
TRUNCATE TABLE temp_data;

6. INSERT ON DUPLICATE KEY UPDATE

Execute the update if there is a primary key or unique key conflict.

7. REPLACE INTO

Delete first, then insert (in case of a conflict).

8. Safety Guidelines

❓ FAQ

📖 Summary

• INSERT Insert data; supports single-row, multi-row, and subqueries
• UPDATE To update data, you must include a WHERE clause
• DELETE Deletes data; must include a WHERE clause
• TRUNCATE Empties the table; it is fast but cannot be rolled back.
• INSERT ON DUPLICATE KEY UPDATE implements UPSERT
• Check and confirm before performing any operations; use transaction protection in the production environment.

📝 Exercises

1. Basic Problem (Difficulty ⭐): Insert 5 test records into the users table.

2. Advanced Problem (Difficulty: ⭐⭐): Implement a visit counter using INSERT ON DUPLICATE KEY UPDATE.

3. Challenge (Difficulty: ⭐⭐⭐): Write a script to securely delete temporary data from 30 days ago, using transaction protection.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us