DELETE in sql

Feb 4, 2022 - 18:46

0 11

What is DELETE Query?

The MySQL DELETE command is used to remove rows that are no longer required from database tables. It removes the entire row from the table and returns the number of rows removed. The Delete command is useful for deleting temporary or obsolete data from your database.

A delete query in MySQL can delete more than one row from a table in a single query. This proves to be an advantage when deleting a large number of rows from a database table.

Once a row in a MySQL row is deleted, it cannot be recovered. Therefore, it is highly recommended that you make database backups before deleting any data from the database. This may allow you to restore the database and view the data later if needed.

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 the MySQL server to delete rows from the table.
The [WHERE clause] is optional and is used to set a filter that limits the number of rows affected by the DELETE query.

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

MySQL Delete Query Example

Before moving on to a more detailed discussion of the DELETE command, let's insert sample data into the movie 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 to the movies table. Before we continue with our lesson, let's review all the movies in our table. The script below does this.

SELECT * FROM `movies`;

Executing the above script gives us the following results.

movie_id	itle	director	year_released	category_id
one	Pirates of the Caribbean 4	Rob Marshall	2011	one
2	Forgetting Sarah Marshall	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	eight
6	Angels and Demons	NULL	2007	6
7	Davinci Code	NULL	2007	6
9	honey mooners	John Schultz	2005	eight
sixteen	67% Guilty	NULL	2012	NULL
eighteen	The Great Dictator	Chalie Chapli	1920	7
nineteen	sample movie	Anonymous	NULL	eight
twenty	movie 3	John Brown	1920	eight

Suppose the Myflix video library no longer wants to rent The Great Dictator to its members and they want it removed from the database. The movie id is 18, we can use the script shown below to remove its row from the movies table.

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

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

Let's see the current state of the movies table.

SELECT * FROM `movies`;

movie_id	title	director	year_released	category_id
one	Pirates of the Caribbean 4	Rob Marshall	2011	one
2	Forgetting Sarah Marshall	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	eight
6	Angels and Demons	NULL	2007	6
7	Davinci Code	NULL	2007	6
9	honey mooners	John Schultz	2005	eight
sixteen	67% Guilty	NULL	2012	NULL
nineteen	sample movie	Anonymous	NULL	eight
twenty	movie 3	John Brown	1920	eight

NOTE:

movie with id 18 was not returned in the query result set.
You cannot delete a single column for a table. You can delete the entire line.

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

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

Running the above script removes the movies with IDs 20 and 21 from our movie 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 a DELETE query.
Once the data has been deleted, it cannot be recovered, so we strongly recommend that you make backup copies before deleting the data.