Delete Query To Delete Rows in MySQL

Delete Query To Delete Rows in MySQL

Learn how to use DELETE SQL query to remove rows of a table in MySQL database.

September 23, 2019

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.

Write a Comment
Click the captcha image to get new code.
Discussion Forum by DISQUS