Categories

Articles in MySQL Category

MySQL Delete Statement

DELETE statement is used for deleting rows from a table. It has following syntax.

DELETE FROM table_name WHERE ... ;

You can run DELETE statements in MySQL command-line, in a GUI tool or with mysqli_query() in a PHP script.

For this article, we are going to use `employee` table mentioned below. SQL commands for creating the table, inserting data and, truncating are available here.

id first_name last_name job_title salary notes
1 Robin Jackman Software Engineer 5500  
2 Taylor Edward Software Architect 7200  
3 Vivian Dickens Database Administrator 6000  
4 Harry Clifford Database Administrator 6800  
5 Eliza Clifford Software Engineer 4750  
6 Nancy Newman Software Engineer 5100  
7 Melinda Clifford Project Manager 8500  
8 Harley Gilbert Software Architect 8000  

We recommend truncating the table and re-entering data after each example so that you can identify the change clearly (by comparing with original table shown above).

Result sets listed in this article have been generated in that way and by executing SELECT statements (SELECT * FROM `employee`) in command-line.

Deleting All the Rows from a Table

Following query deletes all the rows from `employee` table.

DELETE FROM `employee`;

Deleting Rows That Match Conditions

Following query deletes the row where ID is 3.

DELETE FROM `employee` WHERE `id` = 3;
+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  8 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+
Except in cases where you want to empty a whole table, make sure you specify a WHERE clause with DELETE statements since otherwise it can lead to critical data losses.

You can specify more than one conditions in WHERE clause using AND, OR and IN() as mentioned in article on SELECT statement.

Preserving Auto-increment Counter

As explained in MySQL manual, except for tables with InnoDB or MyISAM storage engine, if you execute a DELETE statement without a WHERE clause, it will reset the auto-increment counter of the table (if it has an auto-increment field). This means if you had eight rows, value for auto-increment field will start from 1 for a new row instead of 9.

If you want to preserve auto-increment counter while deleting all the rows then you have to use a WHERE clause that’s applicable to all the rows like below.

DELETE FROM `employee` WHERE 1;

When executing above query, MySQL will delete rows one by one and empty whole table which can be slightly slow compared to the statement with no WHERE clause.

Storage engine of `employee` table used in this article is InnoDB and therefore it will preserve auto-increment counter even if you didn’t use a WHERE clause.

Limiting Number of Rows to Delete

Think that accidently three rows have been inserted for “Harley Gilbert” as shown in following result set.

+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  8 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
| 10 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

You can delete two rows of “Harley Gilbert” and keep only one with following query.

DELETE FROM `employee` WHERE `first_name` = 'Harley' AND `last_name` = 'Gilbert' LIMIT 2;
+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
| 10 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

Setting the Order of Deletion

Like for UPDATE statements, MySQL doesn’t have a guaranteed order for deletions. However you can specify the order of deletion with an ORDER BY clause.

For an example, think that you want to keep the first row and delete last two rows in the case mentioned above (having three rows for “Harley Gilbert”). You can do that with following SQL query.

DELETE FROM `employee` WHERE `first_name` = 'Harley' AND `last_name` = 'Gilbert' ORDER BY `id` DESC LIMIT 2;
+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  8 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

Using TRUNCATE TABLE Statement

TRUNCATE TABLE statement can also be used to delete all the rows of a table like below.

TRUNCATE TABLE `employee`;

TABLE part is optional and query can also be written like below.

TRUNCATE `employee`;

Differences Between DELETE FROM and TRUNCATE TABLE

MySQL manual explains a list of differences between DELETE FROM and TRUNCATE TABLE. Following are few important items to note.

  • TRUNCATE TABLE always returns zero (DELETE FROM returns number of rows deleted).
  • TRUNCATE TABLE doesn’t preserve the auto-increment counter of a table and resets it irrespective of the storage engine of the table (Applicable for InnoDB and MyISAM too).
  • TRUNCATE TABLE can not use clauses like WHERE and can only be used for the purpose of empting a whole table.
  • TRUNCATE TABLE drops and re-create the table which is faster than deleting rows one by one (especially for large tables).

Using MySQL Delete Statement in PHP

Following PHP script shows how to use a delete statement in PHP. Two commented out queries correspond to first two conditions of the if-else block. You can try out all three queries by commenting out two queries at a time.

After running a delete query in mysqli_query(), you can get number of rows deleted from mysqli_affected_rows().

<?php 

/* Change database details according to your database */
$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db'); 

//$query = "DELETE FROM `employee` WHERE `id` = 9";
//$query = "DELETE FROM `employee` WHERE `id` = 8";
$query = "DELETE FROM `employee` WHERE `id` IN(6, 7)"; 

if (mysqli_query($dbConnection, $query)) { 

    $rowsAffected = mysqli_affected_rows($dbConnection);

    if ($rowsAffected == 0) {
        echo "No rows were deleted";
    } elseif ($rowsAffected == 1) {
        echo "1 row was deleted";
    } elseif ($rowsAffected > 0) {
        echo "$rowsAffected rows were deleted";
    }

} else {

    echo "Error occurred: " . mysqli_error($dbConnection);

} 

?>
Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.