MySQL – DELETE Record
A MySQL DELETE statement is used to remove records from a MySQL table that are no longer required. The following query in MySQL deletes a full row from the table and calculates the number of rows that have been deleted. It is also possible to delete more than one record from the table within a single query, which is beneficial when removing large numbers of records. With the delete statement, we can also remove data based on conditions.
Once the records have been deleted using this query, they cannot be recovered. It is therefore recommended that you create a backup of your database before deleting any records from the table. The database backups allow us to restore the data at any time in the future.
Syntax:
DELETE FROM table_name WHERE condition;
The above statement requires us to first specify the name of the table from which we wish to delete data. Secondly, we need to specify the condition for deleting records in the WHERE clause, which is optional. This query will remove whole records from the database table if we do not include the WHERE clause.
The JOIN clause must be added with the DELETE statement if we wish to delete records from multiple tables using a single DELETE query.
In order to delete all records from a table without knowing how many rows have been deleted, we must use the TRUNCATE TABLE statement which is more efficient.
Example:
DELETE FROM employees WHERE emp_no=10001;
When the query is executed, it will return the output as shown in the following image. After deleting the record, verify the table by using a SELECT statement: