MySQL Data Manipulation: Using INSERT, UPDATE, and DELETE Commands

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.

Alexander S. Ricciardi

April 30, 2025

---

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

INSERT 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/