MySQL DELETE Query: How to Delete Row from a Table

Details

What is the DELETE Query?

MySQL DELETE command is used to delete rows that are no longer required from the database tables. It deletes the whole row from the table and returns count of deleted rows. Delete command comes in handy to delete temporary or obsolete data from your database.

The Delete query in MySQL can delete more than one row from a table in a single query. This proves to be advantages when removing large numbers of rows from a database table.

Once a Delete row in MySQL row has been deleted, it cannot be recovered. It is therefore strongly recommended to make database backups before deleting any data from the database. This can allow you to restore the database and view the data later on should it be required.

How to Delete a row in MySQL

To delete a row in MySQL, the DELETE FROM statement is used: 

DELETE FROM `table_name` [WHERE condition];

HERE

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

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

Example of MySQL Delete Query

Before we go into more details discussion the DELETE command, 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', 'Chalie Chaplie', 1920, 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 we go any further into our lesson, let's get all the movies in our table. The script shown below does that. 

SELECT * FROM `movies`;

Executing the above script gives us the following results. 




 
movie_iditledirectoryear_releasedcategory_id

 
1Pirates of the Caribean 4Rob Marshall20111

 
2Forgetting Sarah MarshalNicholas Stoller20082

 
3X-MenNULL2008NULL

 
4Code Name BlackEdgar Jimz2010NULL

 
5Daddy's Little GirlsNULL20078

 
6Angels and DemonsNULL20076

 
7Davinci CodeNULL20076

 
9Honey moonersJohn Schultz20058

 
1667% GuiltyNULL2012NULL

 
18The Great DictatorChalie Chaplie19207

 
19sample movieAnonymousNULL8

 
20movie 3John Brown19208

 Let's suppose that the Myflix video library no longer wishes to be renting out "The Great Dictator" to its members and they want it removed from the database. Its movie id is 18, we can use the script shown below to delete its row from the movies table. 

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

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

Let's see the current status of movies table.

SELECT * FROM `movies`;

 
movie_idtitledirectoryear_releasedcategory_id

 
1Pirates of the Caribean 4Rob Marshall20111

 
2Forgetting Sarah MarshalNicholas Stoller20082

 
3X-MenNULL2008NULL

 
4Code Name BlackEdgar Jimz2010NULL

 
5Daddy's Little GirlsNULL20078

 
6Angels and DemonsNULL20076

 
7Davinci CodeNULL20076

 
9Honey moonersJohn Schultz20058

 
1667% GuiltyNULL2012NULL

 
19sample movieAnonymousNULL8

 
20movie 3John Brown19208

NOTE:

Let's say we  have a list of movies we want to delete . We can use the WHERE clause along with IN. 

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

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

Summary

 

YOU MIGHT LIKE: