MySQL DELETE Query: How to Delete a Row from Table

What is the DELETE Query?

The MySQL DELETE statement is used to remove rows that are no longer required from a database table. It deletes the whole row from the table and returns the count of deleted rows. The DELETE statement comes in handy when you need to clear temporary or obsolete data from your database.

The DELETE query in MySQL can remove more than one row from a table in a single execution. This proves advantageous when you are removing large numbers of rows from a database table.

Once a row has been deleted, it cannot be recovered. Always make a database backup before deleting data, so the database can be restored and reviewed later if required.

How to Delete a Row in MySQL

With the purpose of the statement clear, the next step is the syntax. To delete a row in MySQL, the DELETE FROM statement is used:

DELETE FROM `table_name` [WHERE condition];

HERE:

• DELETE FROM `table_name` tells the MySQL server to remove rows from the named table.
• [WHERE condition] is optional and is used to apply a filter that restricts the number of rows affected by the DELETE query.

If the WHERE clause is not used in the MySQL DELETE query, then all the rows in a given table will be deleted.

⚠️ Warning: Run the filter as a SELECT first. If it returns exactly the rows you intend to remove, the matching DELETE is safe. Full syntax is documented in the MySQL DELETE Statement manual.

Example of MySQL Delete Query

The next section builds a small data set and then deletes from it.

Step 1: Insert Sample Data

Before we go into a more detailed discussion about the SQL DELETE statement, let’s insert some sample data into the movies table to work with.

INSERT INTO `movies` (`title`, `director`, `year_released`, `category_id`) VALUES ('The Great Dictator', 'Charlie Chaplin', 1940, 7);
INSERT INTO `movies` (`title`, `director`, `category_id`) VALUES ('sample movie', 'Anonymous', 8);
INSERT INTO `movies` (`title`, `director`, `year_released`, `category_id`) VALUES ('movie 3', 'John Brown', 1920, 8);

Executing the above script adds three (3) movies into the movies table. Before going further, let’s list all the movies in our table.

SELECT * FROM `movies`;

Executing the above script gives us the following results. The three newly inserted rows are highlighted.

Step 2: Delete a Single Row

Suppose the Myflix video library no longer wishes to rent out “The Great Dictator” and wants it removed from the database. Its movie id is 18, so the script below deletes that row.

DELETE FROM `movies` WHERE `movie_id` = 18;

Executing the above script in MySQL WorkBench against the Myflix database deletes the movie with id 18 from the table.

Let’s now check the current state of the movies table.

SELECT * FROM `movies`;

NOTE:

• The movie with id 18 is no longer returned in the query result set.
• You cannot delete a single column from a table with DELETE. The statement removes an entire row. To clear one field, use an UPDATE query that sets the column to NULL.

Step 3: Delete Multiple Rows with IN

Let’s say we have a list of movies we want to delete. Rather than running one statement per row, we can combine the WHERE clause with the IN operator.

DELETE FROM `movies` WHERE `movie_id` IN (20,21);

Executing the above script deletes the movies with IDs 20 and 21 from our movies table in one pass.

DELETE vs TRUNCATE vs DROP in MySQL

DELETE is not the only way to remove data from MySQL. Beginners often reach for it when a faster command would fit better, so the table below compares the three removal commands.

Use DELETE when you need a filter or the ability to roll back. Use TRUNCATE TABLE when you want to empty a table quickly and reset its AUTO_INCREMENT counter. Use DROP only when the table itself is no longer needed, because it removes the definition along with the data.

FAQs