This content originally appeared on Level Up Coding - Medium and was authored by Alexander S. Ricciardi
This article explains how to manipulate data using MySQL tables and the Data Manipulation Language (DML) commands: INSERT to add new rows, UPDATE to modify existing rows, and DELETE to remove rows, providing syntax and illustrated examples for each.
One of the core functions of a Relational Database Management System (RDBMS) is to allow users to manipulate data, such as inserting data into a table, modifying that data, and then deleting whatever data they no longer want to store. These RDBMS functions are handled by the Data Manipulation Language (DML), a subset of the Structured Query Language (SQL), and implementations of SQL like MySQL (Murach, 2019). Note that the Data Definition Language (DDL) is also a subset of SQL, which handles the definition or modification of the structure of database objects (schemas) like tables and indexes. In other words, DML is used to manipulate the data within schemas, and DDL is used to manipulate the schemas. This article focuses on the DML operations (INSERT, UPDATE, and DELETE) used to manipulate data within MySQL tables, providing examples.
DML Operations
The DML operations for modifying data in MySQL tables are handled by the statements (also referred to as commands): INSERT, UPDATE, and DELETE.
- The INSERT command adds new rows of data.\
- The UPDATE command modifies data in existing rows.
- The DELETE command removes rows entirely.
Note that these commands manipulate rows, not columns. However, the command SELECT is used for querying and retrieving data from a table based on the table column (Watt & Eng, 2014).
These commands (INSERT, UPDATE, DELETE) are often used in combination with the SELECT command and the WHERE clause to manipulate data by querying the table columns and selecting rows based on a specific condition(s). Note that the WHERE clause is responsible for filtering rows, also called records, based on specific conditions (Kathuria, 2021).
Data Manipulation Examples
To illustrate the examples on how to use the INSERT, UPDATE, and DELETE commands, the following products table is going to be used.
Figure 1
Products Table
Note: The figure table illustrates a product table of various types of guitars.
The INSERT Commands
The INSERT command is used to insert rows/records (to store new data) in tables. Different syntax variations of the INSERT command exist in MySQL:
Using VALUES
NSERT INTO table_name
(column1, column2, ...) -- the columns list
VALUES
(value1a, value2a, ...), -- Values for the first row
(value1b, value2b, ...), -- Values for the second row
(value1c, value2c, ...), -- Values for the third row
...
(value1n, value2n, ...); -- Values for the last row
Using SET
INSERT INTO table_name
SET column1 = value1,
column2 = value2,
... ;
With the SET command, only one value can be inserted by column.
Using SELECT
INSERT INTO table_name (column1, column2,...)
SELECT source_column1, source_column2,...
FROM source_table
WHERE condition;
Here, SELECT is used to insert columns from a source table column where a condition is met.
INSERT Example using the products table:
Figure 2
INSERT Example
Note: The figure illustrates the SQL code for inserting a new guitar item in the products table.
The UPDATE Commands
The UPDATE command is used to update existing rows/records’ values (to modify existing data) in tables. Syntax:
UPDATE table_name
SET column1 = new_value1,
column2 = new_value2
WHERE ‘condition’; -- The condition determines which rows are updated
The WHERE clause is used to update specific records’ values based on a specific condition.
UPDATE Example using the products table:
Figure 3
UPDATE Example
Note: The figure illustrates the SQL code for adding +10 to the discount_percent for all items with a category_id = 1.
The DELETE Commands
The DELETE command is used to delete rows/records (to delete existing data) in tables. Syntax:
DELETE FROM table_name
WHERE ‘condition’; -- Specifies row(s) to delete
Note: The figure illustrates the SQL code for deleting all items with a category_id = 1
DELETE Example using the products table:
Figure 4
DELETE Example
Note: The figure illustrates the SQL code for deleting all items with a category_id = 1.
Hazards of Using UPDATE and DELETE
When using the UPDATE and DELETE commands, users need to be extremely careful, as these commands are very powerful and consequently come with significant risks. Accidentally misusing these commands can result in widespread data corruption or data loss. Some of these hazards include
- Missing or incorrect WHERE clause resulting in the UPDATE or DELETE commands being intentionally applied to all rows.
- Data loss caused by incorrect logic or use of the WHERE clause condition and the UPDATE command, which may be potentially fixed, and the DELETE command, which often results in irreversible loss of data.
To summarize, the INSERT, UPDATE, and DELETE commands are DML operations in MySQL for adding, modifying, and removing data within tables. The INSERT syntax can take various forms. The UPDATE and DELETE commands require particular attention due to their potential to modify or destroy existing data on a large scale.
References:
Kathuria, H. (2021, November 9). How to write a WHERE clause in SQL. Learn SQL. https://learnsql.com/blog/where-clause-in-sql/
Murach, J. (2019). Chapter 1: An introduction to relational databases. Murach’s MySQL (3rd ed.). Murach Books. ISBN: 9781943872367
Watt, A., & Eng, N. (2014). Chapter 16 SQL Data Manipulation Language. Database Design — 2nd Edition. Open textbooks, British Columbia University Open Campus. https://opentextbc.ca/dbdesign01/chapter/chapter-sql-dml/
Originally published at https://www.alexomegapy.com on April 30, 2025.
MySQL Data Manipulation: Using INSERT, UPDATE, and DELETE Commands was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Alexander S. Ricciardi
Alexander S. Ricciardi | Sciencx (2025-05-02T00:44:19+00:00) MySQL Data Manipulation: Using INSERT, UPDATE, and DELETE Commands. Retrieved from https://www.scien.cx/2025/05/02/mysql-data-manipulation-using-insert-update-and-delete-commands/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.