This Tutorial Explains How to use MySQL DELETE Statement to Delete Data from a Table or Delete the Entire Table & Difference Between Truncate & Delete Commands:
In MySQL, DELETE is a Data Manipulation Language or DML, as we know.
As the name itself suggests, the command is used to delete rows from the table. Using this command, we can delete one or more unwanted rows in one single transaction. There are multiple ways to delete records from a table using the DELETE command.
In this tutorial, we will discuss all these in detail along with simple examples.
=> Click here for the complete MySQL tutorial series
Table of Contents:
MySQL DELETE Command
There is an important factor to keep in mind before we proceed ahead. Any row or multiple rows that we delete using the DELETE command can never be retrieved.
Thus, in order to avoid scenarios where a row is deleted by mistake, it’s advisable to take a backup of the table before executing the DELETE statement. We can later use this backup to restore the table in case of any mistakes.
Note: We are using MySQL version 8.0. You can download it from here.
MySQL Delete Syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tablename[.*] [, tablename[.*]] [WHERE condition] [ORDER BY] [LIMIT row_count] ;
Syntax Explanation:
- The syntax starts with the keyword “DELETE FROM”, thereby informing the MySQL Server about the type of activity to be performed. This is a mandatory keyword and cannot be omitted.
- Next comes the name of the table in which the DELETE operation has to be performed. This is also mandatory and cannot be omitted. We can mention one or more than one table names here.
- Next, we can mention any condition that controls the eligibility of the rows to be deleted. This is an optional clause. The keyword here is WHERE.
- Followed by the WHERE clause is the ORDER BY clause, which enforces the rows to be deleted in a particular order, either ascending or descending, on a column(s). This is an optional clause.
- LIMIT clause follows the ORDER BY clause. It puts a limit on the number of rows that the DELETE statement can remove from the table.
Modifiers In DELETE Statement
#1) LOW_PRIORITY:
This modifier informs the MySQL Engine to delay the execution of the DELETE statement until such a time when there are no connections reading from the table that we are attempting to DELETE from.
This helps in achieving consistency across all the other operations that will be performed on that table. This also helps in avoiding table locking issues.
#2) QUICK:
Whenever we delete a row, the corresponding index also gets deleted. Reclaiming the space at the time of execution of the DELETE statement consumes the processor time. If we use the QUICK keyword, then the corresponding index space remains unutilized and can be utilized when new records that suit the index range that was deleted earlier are inserted.
For Example, consider an employee table where the employee ID is the index. Whenever an employee leaves an organization, we need to delete the corresponding record. If we use the QUICK clause then the corresponding index space will remain unutilized.
When a new employee joins, he/she will get a new employee number and not the same number of the person who left. In such examples, it’s not advisable to use QUICK.
MYSQL DELETE Example
Given below is a sample table created in MYSQL.
Schema Name: pacific
Table Name: employees
Column Names:
empNum: Holds integer values for the employee number.
lastName: Holds varchar values for the last name of the employee.
firstName: Holds varchar values for the first name of the employee.
email: Holds varchar values for the email ID of the employee.
deptNum: Holds varchar for the department ID that an employee belongs to.
salary: Holds decimal values of the salary of each employee.
start_date: Holds the date values for the joining date of the employee.
Schema Name: pacific
Table Name: employees_history
Column Names:
empNum: Holds the integer values for employee number.
lastName: Holds varchar values for the last name of the employee.
firstName: Holds varchar values for the first name of the employee.
email: Holds varchar values for the email ID of the employee.
deptNum: Holds varchar for the department ID that an employee belongs to.
salary: Holds decimal values of the salary of each employee.
start_date: Holds date values for the joining date of the employee.
Schema Name: pacific
Table Name: departments
Column Names:
deptNum: Holds varchar for the department ID within an organization.
city: Holds the name of the city in which the departments work from.
country: Holds the name of the country corresponding to the city.
bonus: Holds the percentage value of the bonus.
MySQL Delete Single Row
The first and easier way of deleting records from the table in deleting the records one-by-one, using a very strong WHERE clause that ensures only one suitable record is deleted. This could be further used to delete a set of similar kinds of rows from the table.
Let’s go through the delete statement and its execution in detail.
Here we will try and remove an employee named ‘Chris Nolan’ with the employee number as 1013.
As depicted in the picture above, the DELETE statement has been executed successfully and deleted one row from the employee’s table.
The output statement below shows the time at which the statement was executed, the MySQL statement that was executed and the number of rows that were affected.
Please be very sure, about the WHERE clause here. If the WHERE clause is incorrect or if you miss out on having a WHERE clause then it may result in data loss. In order to avoid such situations, in production, there is a practice of periodic data backups or unloads.
In order to verify the output of this DELETE statement, let’s execute the SELECT statement on this table with the empNum as 1013.
The output results show NULL value for all the columns, implying that no record exists for such an empNum. The Output of the query below shows that 0 row(s) have been returned, implying that the record has been deleted by the execution of the DELETE statement above.
Query:
DELETE FROM employees WHERE empNum = 1013 ;
Table Snapshot After:
| empNum | lastName | firstName | deptNum | Salary | |
|---|---|---|---|---|---|
| NULL | NULL | NULL | NULL | NULL | NULL |
MySQL DELETE Using ORDER BY And LIMIT Clause
ORDER BY and LIMIT are two clauses that we have discussed above. The former helps in deciding the order in which the records should be purged, either delete records with empNum in ascending or delete records with the salary in descending order. The latter helps in restricting the number of records that this transaction can delete.
For Example, if we want to delete only 2 employees belonging to dept number 4, who are high salary takers. Then there are three parts to this query.
- First, we need to purge employees from dept number 4. This will be handled by a WHERE clause.
- Next, we need to purge employees who are earning high salaries. This will be handled by the ORDER BY clause on the salary column in descending order.
- Lastly, we need to purge only two employees. This will be handled by the LIMIT clause.
Let’s have a look at the DELETE query and see the results. Before doing so, let’s first identify what are the exact records that we want to delete. We will use the above conditions with a SELECT statement to get the records that we want to delete and later execute the same query to verify if those records have been deleted or not.
In the above SELECT query, we have satisfied all the three conditions that we talked above using the WHERE, ORDER BY and LIMIT clause. The query gave us 2 records, one with empNum 1010 and another with 1007. Now, we will execute the DELETE query to ensure that these two records are deleted.
As depicted in the picture above, the DELETE statement has executed successfully and deleted two rows from the employee’s table. The output statement below shows the time at which the statement was executed, the MySQL statement that was executed and the number of rows that were affected.
In order to verify the output of this DELETE statement, let’s execute the SELECT for empNum 1010 and 1007. We cannot use the same SELECT query as used above as that would show the other employees under deptNum 4.
The output results show NULL value for all columns, implying that no record exists for such employee numbers. The Output of the query below shows that 0 row(s) have been returned, implying that the record has been deleted by the execution of the DELETE statement above.
Query:
DELETE FROM employees WHERE deptNum = 4 ORDER BY salary DESC LIMIT 2 ;
Table Snapshot After:
| empNum | lastName | firstName | deptNum | Salary | |
|---|---|---|---|---|---|
| NULL | NULL | NULL | NULL | NULL | NULL |
MySQL DELETE Using Select Clause
Next, we will go through the usage of the SELECT clause while deleting records from one table.
Consider the following scenario:
We have two sets of tables: employees and departments. deptNum is like a primary key in the department’s table and foreign key in the employee’s table. This means that if an employee is assigned a department then it has to be from one of the departments in the departments’ table.
Now, we have to delete those records from the department’s table where no employee has been assigned so far. We can do this by having a SELECT clause in the sub-query of a DELETE query.
Let’s first identify the records that we want to delete:
The above SELECT query is having a subquery.
The query functioning is as follows:
The query pulls all records from the department’s table where the deptNum does not exist in the employee’s table. The join is performed in the sub-query on the deptNum of the two tables.
Now let’s run the DELETE query and see if we can delete these two records from the department’s table as no employee is assigned to these departments.
As depicted in the above picture, the delete statement has deleted 2 rows from the department tables that have no employee assigned to it.
In order to verify the output of this DELETE statement, let’s execute SELECT on the table and see what we have in the output.
The output message of the above verification query says “0 row(s) returned”. This implies that the rows with deptNum 6 and 7 are deleted. The output also shows no records instead of showing NULL values as the records have been deleted.
Query:
DELETE FROM departments
WHERE NOT EXISTS (SELECT deptNum
FROM employees
WHERE departments.deptNum = employees.deptNum
)
;
MySQL DELETE Entire Data From Table
Next, we will look at the scenario where we have to delete all the rows or records from a table.
In the production situation, you may never encounter such a situation. But this kind of thing would be required in the development area where you have to test your code with multiple different scenarios and you may want to re-create a fresh set of test data by deleting the records existing in the table.
First, let’s have a look at the data that we intend to delete. We will attempt to delete data from the employees_history table.
Given below is the currently existing data in the table.
Here we have 18 rows in the employees_history table. Let’s now go ahead and delete all these in one single transaction. The query is almost similar to the one that we discussed in the first section. The only change is that we need to remove the WHERE clause from the query so that there is no restriction on the number of rows that we want to delete.
As depicted in the above picture, the delete statement has been executed successfully and deleted all the 18 rows from the employees_history table. The output statement below shows the time at which the statement was executed, the MySQL statement that was executed and the number of rows that were affected.
In order to verify the output of this DELETE statement, let’s execute SELECT on the table and see what we have in the output.
The output results show NULL value for all columns, implying that no record exists for such employee numbers. The Output of the query below shows that 0 row(s) have been returned, implying that all the records have been deleted by the execution of the above DELETE statement.
Query:
DELETE FROM employees_history ;
Table Snapshot After:
| empNum | lastName | firstName | deptNum | Salary | |
|---|---|---|---|---|---|
| NULL | NULL | NULL | NULL | NULL | NULL |
MySQL DELETE Entire Table
Now, we will look at the scenario where we have to delete the table itself. In SQL terms, we call it DROP the table. It does not matter if the table has any data or not. It will simply drop the table along with the data if any.
In the above section, we deleted data from the employees_history table. In this section, we will drop the employees_history table itself.
Following is the command along with its output:
As depicted in the above picture, the DROP statement has been executed successfully and deleted the table employees_history from the system catalog of the MySQL database.
If you take a look at the message, it will show “0 row(s) affected”. This is because we had deleted all the 18 rows in the previous section. If this table had any rows then those numbers of rows would be affected, which implies deleted.
In order to verify the output of this DROP statement, let’s execute SELECT on the table and see what we have in the output.
The output message of the above verification query says that the table does not exist. This validates our execution of the above DROP statement to drop the table.
Query:
DROP TABLE employees_history ;
MySQL Truncate Command
We already discussed deleting all records from the table using the DELETE command. The same could be achieved using the TRUNCATE statement as well.
For this example, let’s re-create the employees_history table and re-populate it with data. The following are the two queries that have been executed to re-create the table and re-populate it:
Query:
CREATE TABLE employees_history LIKE employees ; INSERT INTO employees_history (SELECT * FROM employees) ;
This is how the employees_history table looks now with data.
Next, we will remove all the records from this table using the TRUNCATE statement.
As shown in the picture, the query is executed successfully. If you take a close look at the “Message” part, you would find that it says “0 row(s) affected”. However, we know that this table has 15 rows in it. Then how come is shows zero?
Reason: Truncate statement does not care to take a count of how many rows are being impacted by its execution. It simply empties the table. This is one of the significant differences between DELETE and TRUNCATE statements.
Let’s verify the execution of the TRUNCATE statement by executing a SELECT query on the employees_history table.
As shown in the picture above, the query has returned no rows and also all the columns are showing NULL values, implying that the TRUNCATE statement that we executed earlier has deleted all records from the table.
Query:
TRUNCATE TABLE employees_history ;
Difference Between TRUNCATE And DELETE Statements
| Sr. No | DELETE | TRUNCATE |
|---|---|---|
| 1 | It’s a DML (Data Manipulation Language). | It’s a DDL (Data Definition Language). |
| 2 | Any AFTER DELETE trigger on the table gets activated when we use DELETE command. | No AFTER DELETE trigger on the table gets activated when we use TRUNCATE command. |
| 3 | A record can be deleted even if there are any foreign key constraints. | Records from table cannot be truncated if there are any foreign key constraints. |
| 4 | Table re-initialization will not happen. | Table will be re-intialized. |
| 5 | WHERE clause can be used. | WHERE clause cannot be used. |
| 6 | This deletes one record at a time and keeps a track of total number of deleted records in logs. | It purges all records at once and does not keep a track of number of records deleted. |
| 7 | This is relatively slowed than TRUNCATE, when compared for deleting entire data from table. | This is relatively faster than DELETE, when compared for deleting entire data from table. |
Referential Integrity And Its Impact On DELETE
Before we being, let’s see what Referential integrity or RI or Foreign Key Constraints is?
Foreign Key constraints are about establishing a relationship or linkage between the parent and the child table. This helps in cross-referencing the data across the tables that are linked to each other. One parent table can have multiple child tables and vice-versa.
For Example, the two tables that we have been discussing so far i.e. employees and departments, are linked to each other using a Foreign Key constraint. This constraint is established by making one column, usually the primary key, as the key column linking two tables.
The deptNum column in the department’s table has been linked to the deptNum column in the employee’s table. In this case, departments are the parent table and employees is the child table.
But, how does this impact the DELETE statements?
In MySQL or in any database, there are certain scenarios to be handled while deleting records from the parent or child tables.
There are multiple reference options that we can discuss:
#1) ON DELETE CASCADE: The rule says that we cannot remove a row from the parent table if it has any references or has a corresponding row in any of the child tables. However, if a parent table has quite a few child tables, then it’s a tedious task to first remove the records from each and every child table and then from the parent table.
For this, there is a workaround called, ON DELETE CASCADE. This is one clause that is added to the CREATE statement of each of the child tables. So, whenever we say delete a row from the parent table, then the MySQL engine would first identify the references of that row in the child tables and remove those records and at last will delete the record from the parent table.
#2) NO ACTION: This is a default option. If the execution of a DELETE statement attempts to delete a record that has references in any of the child tables, then with this option the execution of the statement will stop and the MySQL transaction will be rolled back to the last commit point.
#3) RESTRICT: Functioning of RESTRICT and NO ACTION are the same. It will stop the execution and issue a rollback.
#4) SET NULL: If the execution of a delete statement attempts to delete a record that has references in any of the child table, then this option will update the column value in all of the child tables as NULL and once done, it will delete the record from the parent table.
#5) SET DEFAULT: If the execution of a DELETE statement attempts to delete a record that has references in any of the child tables, then this option will update the column values to the default value as defined in the CREATE statement of the table.
Frequently Asked Questions And Answers
Q #1) How to Delete Data from the Table in MySQL?
Answer: The syntax of delete command to delete only a selected row of data is given below.
DELETE FROM table_name WHERE condition;
Q #2) How to Delete all the Data from the Table in MySQL?
Answer: The syntax of the DELETE command to delete all the rows from the table is:
DELETE * FROM table_name;
Q #3) How to Delete the Table from the Database in MySQL?
Answer: The drop command can be used to delete the table from the database. User can replace the table_name with the table that needs to be deleted.
DROP TABLE table_name;
Q #4) How to Delete the Database in MySQL?
Answer: The drop command can be used to delete the database.
DROP Database db_name;
Users can replace the db_name with the database name that needs to be deleted.
Q #5) What is ON DELETE CASCADE in MySQL?
Answer: When the parent record is deleted, ON DELETE CASCADE creates a matching child record to be deleted. Thus, the effect of deletion is cascaded from the parent to the child. This can be useful in performing multiple-table delete.
Q #6) Why TRUNCATE is considered as a DDL statement?
Answer: TRUNCATE statement actually drops and re-creates the table along with the metadata of the table. Thus, it’s a DDL statement.
Q #7) Can TRUNCATE be rolled back?
Answer: No, TRUNCATE is an auto-commit statement if executed standalone. This means that it cannot be rolled back as the commit is also executed along with it. But if it’s a part of a transaction, then it can be rolled back using the SQL Log Files.
Q #8) Can DELETE statements be rolled back?
Answer: Yes, DELETE statements can be rolled back. Just execute the ROLLBACK command before executing COMMIT.
Q #9) Can a column be deleted using the DELETE command?
Answer: Column addition or deletion are DDL commands. An ALTER statement should be used to DROP a column from the table.
Conclusion
In this tutorial, we have learned different ways of executing DELETE statements in MySQL.
In a nutshell, we saw:
- MySQL Delete Single Row
- MySQL Delete Using ORDER BY And Limit Clause
- MySQL Delete Using Select Clause
- MySQL Delete Entire Table
- MySQL Truncate
- Difference between TRUNCATE and DELETE statements
- Referential Integrity And Its Impacts On DELETE
We can use either of the above, based on the requirement.
Happy Reading!!