MySQL – DELETE Query

MySQL – DELETE Query

If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM. You can use this command at the mysql> prompt as well as in any script like PHP.

Syntax

The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.

DELETE FROM table_name [WHERE Clause]
  • If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
  • You can specify any condition using the WHERE clause.
  • You can delete records in a single table at a time.
    The WHERE clause is very useful when you want to delete selected rows in a table.

    Deleting Data from the Command Prompt

    This will use the SQL DELETE command with the WHERE clause to delete selected data into the MySQL table – tutorials_tbl.

    Example

    The following example will delete a record from the employees whose id is 3.

    root@host# mysql -u root -p password;
    Enter password:
    mysql> use mydb;
    Database changed
    mysql> delete from employees where id = 3;
    Query OK, 1 row affected (0.14 sec)
    mysql>

    Deleting Data Using a PHP Script

    You can use the SQL DELETE command with or without the WHERE CLAUSE into the PHP function – mysqli_query(). This function will execute the SQL command in the same way as it is executed at the mysql> prompt.

    Example

    Try the following example to delete a record from the employees whose id is 3.

    <title>Deleting MySQL Database</title>
    alert('data deleted succesfully!');
    num_rows > 0) {
    while($row = mysqli_fetch_assoc($result)) {
    echo "Sr. no:{$row['id']}  <br> ".
    "Company name:{$row['company']}  <br> ".
    "First Name: {$row['first_name']} <br> ".
    "Last Name: {$row['last_name']} <br> ".
    "Job Title: {$row['job_title']} <br> ".
    "Mobile Number: {$row['mobile_phone']} <br> ".
    "Email : {$row['email_address']} <br> ".
    "--------------------------------<br>";
    } }
    else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
    ?>
MySQL – UPDATE Query (Prev Lesson)
(Next Lesson) MySQL – LIKE Clause
', { 'anonymize_ip': true });