(SQL examples for Beginners)
In this end-to-end example, you will learn – SQL Tutorials for Business Analyst: MySQL Tutorials for Business Analyst: MySQL DELETE Query.
MySQL DELETE Query with Example
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 rows in a MySQL table, use the DELETE FROM statement:
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 DELETE query.
If the WHERE clause is not used in the DELETE MySQL 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_id | itle | director | year_released | category_id |
---|---|---|---|---|
1 | Pirates of the Caribean 4 | Rob Marshall | 2011 | 1 |
2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
3 | X-Men | NULL | 2008 | NULL |
4 | Code Name Black | Edgar Jimz | 2010 | NULL |
5 | Daddy’s Little Girls | NULL | 2007 | 8 |
6 | Angels and Demons | NULL | 2007 | 6 |
7 | Davinci Code | NULL | 2007 | 6 |
9 | Honey mooners | John Schultz | 2005 | 8 |
16 | 67% Guilty | NULL | 2012 | NULL |
18 | The Great Dictator | Chalie Chaplie | 1920 | 7 |
19 | sample movie | Anonymous | NULL | 8 |
20 | movie 3 | John Brown | 1920 | 8 |
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_id | title | director | year_released | category_id |
---|---|---|---|---|
1 | Pirates of the Caribean 4 | Rob Marshall | 2011 | 1 |
2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
3 | X-Men | NULL | 2008 | NULL |
4 | Code Name Black | Edgar Jimz | 2010 | NULL |
5 | Daddy’s Little Girls | NULL | 2007 | 8 |
6 | Angels and Demons | NULL | 2007 | 6 |
7 | Davinci Code | NULL | 2007 | 6 |
9 | Honey mooners | John Schultz | 2005 | 8 |
16 | 67% Guilty | NULL | 2012 | NULL |
19 | sample movie | Anonymous | NULL | 8 |
20 | movie 3 | John Brown | 1920 | 8 |
NOTE:
- the movie with id 18 has not been return in the query result set.
- you cannot delete a single column for a table. You can delete an entire row.
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
- The delete command is used to remove data that is no longer required from a table.
- The “WHERE clause” is used to limit the number of rows affected by the DELETE query.
- Once data has been deleted, it cannot be recovered, it is therefore strongly recommend make backups before deleting data.
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.
MySQL Tutorials for Business Analyst: How to use Index in MySQL