The DELETE command deletes table rows from the specified table, and returns the quantity deleted. Access the quantity deleted with the ROW_COUNT() function. A WHERE clause specifies rows, and in its absence, all rows are deleted. A LIMIT clause controls the number of rows deleted.

In a DELETE statement for multiple rows, it deletes only those rows satisfying a condition; and LIMIT and WHERE clauses are not permitted. DELETE statements allow deleting rows from tables in different databases, but do not allow deleting from a table and then selecting from the same table within a subquery.

Review the following DELETE syntax −

DELETE FROM table_name [WHERE …]

Execute a DELETE command from either the command prompt or using a PHP script.

The Command Prompt

At the command prompt, simply use a standard command −

root@host# mysql –u root –p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> DELETE FROM products_tbl WHERE product_id=133;
mysql> SELECT * from products_tbl WHERE ID_number=''133'';
ERROR 1032 (HY000): Can''t find record in ''products_tbl''

PHP Delete Query Script

Use the mysql_query() function in DELETE command statements −

<?php
   $dbhost = ''localhost:3036'';
   $dbuser = ''root'';
   $dbpass = ''rootpassword'';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die(''Could not connect: '' . mysql_error());
   }

   $sql = ''DELETE FROM products_tbl WHERE product_id = 261'';
   mysql_select_db(''PRODUCTS'');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die(''Could not delete data: '' . mysql_error());
   }

   echo "Deleted data successfullyn";
   mysql_close($conn);
?>

On successful data deletion, you will see the following output −

mysql> Deleted data successfully
mysql> SELECT * from products_tbl WHERE ID_number=''261'';
ERROR 1032 (HY000): Can''t find record in ''products_tbl''