Verification: a143cc29221c9be0

Php and mysql delete query

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).