May 16, 2021 MariaDB
The DELETE command removes the table row from the specified table and returns the number of deleted. A ccess the number ROW_COUNT deleted using the function (). T he WHERE clause specifies rows, and if there are no rows, deletes all rows. T he LIMIT clause controls the number of rows deleted.
In a multi-line DELETE statement, it deletes only those lines that meet the criteria; L IMIT and WHERE clauses are not allowed. T he DELETE statement allows rows to be deleted from tables in different databases, but not from tables, and then selected from the same table in the subs query.
Check out the following DELETE syntax -
DELETE FROM table_name [WHERE …]
Executed the DELETE command from a command prompt or using a PHP script.
At the command prompt, simply use the 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'
Use the mysql_query() function in the DELETE command statement -
<?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 successfully "; mysql_close($conn); ?>
After successfully deleting the data, 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'