How to Delete a Row in MySQL

If the user wants to remove a row from the table in MySQL, then the user needs to implement the DELETE query.

What is DELETE Query?

The user implements the MySQL DELETE query to remove the rows that are no longer needed in the database table by the user. This command will delete the entire row from the table and return the number of rows deleted by the operation. Delete query is frequently used to remove temporary or data no longer required from the database.

The Delete query can be implemented to remove multiple rows from the existing table by executing a single query. The Delete command is helpful as it enables the user to remove several rows from the database table.

A disadvantage of using the Delete command is permanently removing the data from the database table. It means the user cannot recover deleted data from the table. It is a standard recommendation to create backups for the database before implementing the Delete query in your database. It ensures that the user can restore the database and check the data later if required by the user. This will also allow the user to restore the data when needed by the user.

How to Delete a Row in MySQL

To remove a row in MySQL, the user must implement the DELETE FROM query statement.

Syntax of DELETE statement

The syntax of the DELETE statement is as follows:

In the above query, the DELETE FROM `name_of_the_table` instructs the MySQL server to delete the provided rows from the table. It is not necessary to use the WHERE condition. The condition allows the user to put specific criteria to ensure that only rows that meet the particular criteria are deleted. The user can limit the number of rows deleted by the DELETE query.

If the user does not implement the Where clause while implementing the DELETE FROM query, all the rows from the table mentioned will be removed from the database.

Implementation of MySQL Delete Query

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.

Add Data in the above Employee table:

Implementing the above SQL commands will create an Employee table. Before implementing the Delete statements command, check the data is successfully added to the MySQL table. Implement the SELECT statement to view the data.

It will return the given output:

Now consider a scenario where the user wants to remove a record of an employee where the first name of the employee is Rahul from the database table. The user can delete the employee from the table by using the fname column of the movie. The user can execute the query below in MySQL to remove the row from the table.

The user can verify the above query is successfully implemented by using the SELECT query to display the updated table. The SELECT query is as follows:

Note:

• You can see that the updated table does not include the record where the fname is Rahul in the result set.
• Though the user may only want to delete a particular column in the table, if the user implements the DELETE command, the entire row of information is deleted.
• It also enables the user to delete multiple data values. The user can implement the DELETE FROM statement with WHERE and IN clauses.
• Implementing the below query will delete the records from the Employee table where the fname is Rahul and Ishika.

Summary

• The user can implement a DELETE command in MySQL that enables them to remove the data that is no more needed in the table.
• The user can add a WHERE clause to the DELETE command to limit the effect of the DELETE query in MySQL.
• Once the user has implemented the DELETE query, then the user cannot recover the data stored in the table. Therefore, it is advised to create a backup before actually implementing the DELETE command in MySQL.