After creating databases and tables, the next step is learning how to manage the records stored inside them. MySQL provides SQL statements for inserting new records, modifying existing records, and removing data.
In this article, you'll learn the data management topics covered in the program, including INSERT INTO, multiple-row inserts, INSERT INTO SELECT, duplicate-key handling, date and datetime insertion, UPDATE, UPDATE JOIN, DELETE JOIN, ON DELETE CASCADE, and TRUNCATE TABLE.
Learning Objectives
Insert records into MySQL tables
Insert multiple records in a single statement
Copy records using INSERT INTO SELECT
Handle duplicate keys during insertion
Use INSERT IGNORE
Insert DATE and DATETIME values
Modify records using UPDATE
Update records using information from another table
Delete related records using DELETE JOIN
Understand ON DELETE CASCADE
Remove all table records using TRUNCATE TABLE
Creating a Practice Table
For the examples in this article, create a Products table.
USE trainingdbc;
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10,2),
stock INT,
created_at DATETIME
);
Check the table structure:
DESCRIBE Products;
Inserting Data with INSERT INTO
The INSERT INTO statement adds new records to a table.
Example
INSERT INTO Products (
product_name,
category,
price,
stock,
created_at
)
VALUES (
'Laptop',
'Electronics',
3500.00,
10,
'2026-07-31 10:30:00'
);
Check the inserted record:
SELECT * FROM Products;
The result may look like:
product_id | product_name | category | price | stock | created_at |
1 | Laptop | Electronics | 3500.00 | 10 | 2026-07-31 10:30:00 |
Inserting Multiple Rows
You can add several records using a single INSERT statement.
INSERT INTO Products (
product_name,
category,
price,
stock,
created_at
)
VALUES
('Keyboard', 'Accessories', 150.00, 25, '2026-07-31 11:00:00'),
('Mouse', 'Accessories', 75.00, 40, '2026-07-31 11:10:00'),
('Monitor', 'Electronics', 850.00, 15, '2026-07-31 11:20:00');
Then:
SELECT * FROM Products;
MySQL adds all three records through a single statement.
Inserting DATE and DATETIME Values
MySQL supports dedicated data types for dates and date-time values.
A DATE value typically follows:
YYYY-MM-DD
Example:
2026-07-31
A DATETIME value can contain both date and time:
YYYY-MM-DD HH:MM:SS
Example:
2026-07-31 14:30:00
For example:
INSERT INTO Products (
product_name,
category,
price,
stock,
created_at
)
VALUES (
'Printer',
'Electronics',
650.00,
8,
'2026-07-31 14:30:00'
);
INSERT INTO SELECT
INSERT INTO SELECT can copy data from one table into another table.
First, create another table:
CREATE TABLE ProductBackup (
product_id INT,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2),
stock INT
);
Then copy information from Products:
INSERT INTO ProductBackup (
product_id,
product_name,
category,
price,
stock
)
SELECT
product_id,
product_name,
category,
price,
stock
FROM Products;
Verify the result:
SELECT * FROM ProductBackup;
This technique is useful when data needs to be copied between compatible tables.
INSERT ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE allows MySQL to update specified values when an insertion conflicts with a primary key or unique key.
For example:
INSERT INTO Products (
product_id,
product_name,
category,
price,
stock
)
VALUES (
1,
'Laptop',
'Electronics',
3600.00,
12
)
ON DUPLICATE KEY UPDATE
price = 3600.00,
stock = 12;
If product_id = 1 already exists, MySQL updates the specified fields instead of inserting another record with the same primary key.
INSERT IGNORE
INSERT IGNORE tells MySQL to continue processing an insert when certain ignorable errors occur.
Example:
INSERT IGNORE INTO Products (
product_id,
product_name,
category,
price,
stock
)
VALUES (
1,
'Laptop',
'Electronics',
3500.00,
10
);
If product_id = 1 already exists, the duplicate primary-key record is not added.
INSERT IGNORE should be used carefully because warnings can otherwise be overlooked.
Updating Data
The UPDATE statement modifies existing records.
Suppose you want to change the price of the Laptop:
UPDATE Products
SET price = 3800.00
WHERE product_id = 1;
Check the result:
SELECT * FROM Products
WHERE product_id = 1;
The price should now show 3800.00.
Updating Multiple Columns
You can modify several fields in the same operation:
UPDATE Products
SET
price = 3900.00,
stock = 15
WHERE product_id = 1;
Always review the WHERE condition carefully before executing an update. Without an appropriate condition, an UPDATE statement can affect multiple records.
UPDATE JOIN
UPDATE JOIN allows information from another table to be used when updating records.
Create a category table:
CREATE TABLE Categories (
category_name VARCHAR(50) PRIMARY KEY,
price_increase DECIMAL(5,2)
);
Insert sample information:
INSERT INTO Categories
VALUES
('Electronics', 10.00),
('Accessories', 5.00);
You can then update product prices based on the matching category:
UPDATE Products p
JOIN Categories c
ON p.category = c.category_name
SET p.price = p.price +
(p.price * c.price_increase / 100);
This demonstrates how related information from another table can influence an update.
Deleting Data
MySQL provides different methods for removing records depending on the requirement.
DELETE JOIN
DELETE JOIN can remove records based on relationships between tables.
For example, suppose discontinued categories are stored in another table:
CREATE TABLE DiscontinuedCategories (
category_name VARCHAR(50) PRIMARY KEY
);
Insert:
INSERT INTO DiscontinuedCategories
VALUES ('Accessories');
Then:
DELETE p
FROM Products p
JOIN DiscontinuedCategories d
ON p.category = d.category_name;
Products belonging to the matching category are deleted.
Important: Always verify the matching records with a SELECT query before executing a DELETE.
ON DELETE CASCADE
ON DELETE CASCADE automatically deletes related child records when the referenced parent record is deleted.
Consider two tables:
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100)
);
Then:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_name VARCHAR(100),
FOREIGN KEY (customer_id)
REFERENCES Customers(customer_id)
ON DELETE CASCADE
);
Insert sample data:
INSERT INTO Customers
VALUES (1, 'John');
INSERT INTO Orders
VALUES (101, 1, 'Laptop');
If you delete:
DELETE FROM Customers
WHERE customer_id = 1;
the related order associated with that customer is also deleted because the foreign key uses ON DELETE CASCADE.
This feature is useful when child records should not remain after their associated parent record has been removed.
TRUNCATE TABLE
TRUNCATE TABLE removes all rows from a table.
Syntax
TRUNCATE TABLE ProductBackup;
After running:
SELECT * FROM ProductBackup;
the table remains available, but its records have been removed.
TRUNCATE TABLE should be used carefully because it is intended to clear the entire table rather than selected records.
UPDATE vs DELETE vs TRUNCATE
Statement | Purpose | Typical Scope |
UPDATE | Modifies existing records | Selected or multiple rows |
DELETE | Removes records | Can target specific records |
TRUNCATE | Clears a table | All rows |
Common Mistakes
Avoid these common issues when modifying database data:
Inserting values using incorrect data types
Omitting required NOT NULL fields
Attempting to insert duplicate primary or unique keys
Using INSERT IGNORE without checking warnings
Running UPDATE without verifying the WHERE condition
Running delete operations without confirming which records will be affected
Using TRUNCATE TABLE when only selected records need to be removed
Using ON DELETE CASCADE without understanding the relationship between parent and child tables
Still need help?
Contact us