Delete Query To Delete Rows in MySQL

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to delete the rows or data of a table in MySQL.

Delete Query

The command DELETE can be used to delete or remove rows of a table. The DELETE command is irreversible, hence we must use it carefully. In case you are remotely logged in to the database, you will also need the DELETE privilege for the table in order to delete the rows from a table.

# DELETE - Syntax
DELETE FROM `table_name` WHERE <single or multiple filter conditions>;

Query Explanation

The MySQL command DELETE can be used to delete rows or data of the given table name where the name of the table is mandatory and WHERE clause is optional.

We have to specify at least one condition using the WHERE clause for the rows to qualify for deletion else the entire table will be erased. All the rows of the table gets deleted without the condition.

Examples

This section provides examples to delete rows of the table using the DELETE command. Use the below-mentioned query to create the user table having the id, first name, last name, and active columns to store user data.

# Create the User Table

CREATE TABLE `enterprise`.`user` (
  `user_id` BIGINT NOT NULL,
  `first_name` VARCHAR(45) ,
  `last_name` VARCHAR(45),
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`));

The below-mentioned query can be used to insert data in the user table.

# Insert Rows - All Columns

INSERT INTO `user` ( `user_id`, `first_name`, `last_name`, `active` ) VALUES 
    ( 1, 'John', 'Smith', 1 ),
    ( 2, 'Rick', 'Jones', 1 ),
    ( 3, 'Catherine', 'Ponting', 0 ),
    ( 4, 'Harsh', 'Upadhyay', 1 ),
    ( 5, 'Tajwinder', 'Singh', 0 );

The above-mentioned query will insert 5 rows in the table having the id, first name, last name, and active columns to represent 5 different users.

Now we will delete the data inserted by us in the user table. It can be done using the DELETE command as shown below.

# DELETE - Delete all the inactive users
DELETE FROM `user` WHERE `active` = 0;

# Result
1	John	Smith		1
2	Rick	Jones		1
4	Harsh	Upadhyay	1

# DELETE - Remove selective users
DELETE FROM `user` WHERE `user_id` = 1;
DELETE FROM `user` WHERE `user_id` IN( 2, 4 );

# Result - All rows deleted

The above-mentioned queries delete the rows from the user table using the WHERE clause. The table rows will be deleted in case the row meets the given conditions.

Now again execute the INSERT query to insert the test data. We can also delete rows using multiple conditions as shown below.

# DELETE - Delete inactive users using first name
DELETE FROM `user` WHERE `first_name` = 'Catherine' AND `active` = 0;

# Result
1	John		Smith		1
2	Rick		Jones		1
4	Harsh		Upadhyay	1
5	Tajwinder	Singh		0

This is how we can delete the data stored in the MySQL tables.