Delete Query in MySQL

The DELETE query in MySQL, a DML command, efficiently removes rows from a table. It checks for constraint violations and can delete multiple records at once, making backups essential as deleted data is irrecoverable.

DELETE Syntax

Syntax of delete query in MySQL:

Single-Row Delete Syntax

If the condition given in the WHERE clause satisfies a single row, then it will result in the deletion of the single row.

Multiple-Row Delete Syntax

If the condition given in the WHERE clause is satisfied by multiple rows, then it will result in the deletion of multiple rows.

Privileges

For deleting rows from a table, the DELETE privilege is required on the table. SELECT privilege is required for those columns which are read-only, like the columns specified in the WHERE clause with the condition for deletion.

Performance

For emptying a table when you are not aware of the number of rows required to be deleted, the TRUNCATE command is faster in comparison to the DELETE query in MySQL as there is no condition in the WHERE clause.

To ensure much time is not taken by the DELETE query in MySQL, the maximum number of rows required to be deleted is specified by using the MySQL-specific LIMIT row_count clause. The LIMIT clause is used in the DELETE query in MySQL for specifying the number of rows required to be deleted. For example, if we want to delete starting 3 rows then we write LIMIT 3. If the count of rows required to be deleted is greater than the row count specified in the LIMIT, then repeat the execution of the DELETE statement to delete the required number of rows until the affected rows count becomes less than the value of the limit.

Syntax of delete query in MySQL with the LIMIT clause:

SQL DELETE Example

Creating a table:

Let us create a table with the name STUDENT and we will store the STUDENT_ROLLNO, STUDENT_NAME, and STUDENT_CLASS in the table. The code for creating the STUDENT table is given below:

Inserting data into the table:

Now let us insert some data into the table.

Fetching data of the table:

First, we will fetch all the data of the table by writing the query given below:

Output:

Deleting a single row from the table:

For deleting a single row, we need to specify the below condition for the deletion of the row.

The above query will delete the row having STUDENT_ROLLNO equal to 2 from the table.

Fetching data of the table:

Output:

Deleting multiple rows from the table:

For deleting multiple rows from the table, specify a condition in the WHERE clause that will be satisfied by multiple rows.

The above query will delete all the rows having STUDENT_CLASS equal to 'I' from the table.

Fetching data of the table:

Output:

Deleting all rows from the table:

For deleting all rows from the table, we do not need to write the WHERE clause in the DELETE statement.

The above query will delete all the rows from the table.

Fetching data of the table:

Output:

The result set is empty.

InnoDB Tables

In MySQL, the LOCK TABLE statement is used to lock tables explicitly to control concurrent access by multiple sessions to the same table. For an InnoDB table, the lock table size may exceed, if multiple rows are required to be deleted from a large table.

The following method can be used for avoiding the issue and for minimizing the time of table locking. The strategy given below does not use the DELETE command at all.

1. Select those rows that are NOT required to be deleted, and insert them into an empty table table_copy which has the same structure as the original table.

2. Use the RENAME table command for renaming the original table to table_old and the copied table table_copy to the original table's name.

3. Now drop the original table which was renamed to table_old.

MyISAM Tables

Rows that are deleted are maintained in the form of a linked list in the MyISAM table. The positions of old rows are used by the subsequent INSERT commands. For reducing the size of the file and for reclaiming unused space, use the myisamchk utility or OPTIMIZE TABLE statement for table reorganization. myisamchk is faster in comparison to the OPTIMIZE TABLE statement but OPTIMIZE TABLE is easier to use.

Multi-Table Deletes

For deleting rows from multiple tables based on the condition specified in the WHERE clause, multiple tables can be specified in the DELETE statement. In multiple-table delete commands, LIMIT or ORDER BY is not allowed to be used.

In the first multiple-table DELETE syntax, rows that are deleted will be only those rows that belong to the tables specified before the FROM clause. In the second multiple-table DELETE statement, all the rows which belong to the tables specified in the FROM clause are deleted.

Or

All three tables specified in the statement are used at the time of searching but matching rows are only deleted from two tables i.e. table table1 and table2.

INNER JOIN is used in the previous example, but the multiple-table DELETE statement also allows us to use other types of join such as RIGHT JOIN, LEFT JOIN, etc. For instance, LEFT JOIN can be used if we want to delete all the rows that are present in table1 but they do not have any match in the table table2.

In the multiple-table DELETE statement, table aliases are always declared in the table reference part. If an alias is declared for a table, then it must be used at the time of table reference. Alias referencing is allowed in all parts of the statement but alias declaration is only allowed in the table reference part.

MCQs

1. Delete query in MySQL is ____ type of statement

   • Option - 1: DDL
   • Option - 2: DML
   • Option - 3: DCL
   • Option - 4: TCL

   Answer: Option - 2.

2. Delete query in MySQL allows to delete _ rows from the table

   • Option - 1: single
   • Option - 2: multiple
   • Option - 3: both single and multiple
   • Option - 4: none

   Answer: Option - 3.

3. For emptying a table, _ command is faster.

   • Option - 1: TRUNCATE
   • Option - 2: DROP
   • Option - 3: DELETE
   • Option - 4: HAVING

   Answer: Option - 1.