Delete Data from a MySQL Database

In MySQL, you can delete data just as easily as you can query it or update it.

SQL DELETE Statement

Use the SQL DELETE statement to delete data from your database. Like the SELECT and UPDATE statements, the DELETE statement accepts a WHERE clause so that you can specify the exact record/s to delete.



  1. The Data

    MySQL Delete Data (before)

    Here's the data before we delete anything.

  2. Delete some Data

    Let's delete red grapes from our list of fruit. Red grapes have been assigned a FruitId of 5 so we will use that in our DELETE statement.

  3. The Result

    MySQL Delete Data (after)

    If we run the above code, then select all rows from the table, we can see that the record has been deleted.

Delete All Records

You can delete all records in a table quite easily. Actually, it's easier to delete all records than to delete one specific record.

  1. The Code

    To delete all records in a table, just omit the WHERE clause. Like this:

  2. The Result

    MySQL Delete Data (oops!)

    Oops… No more records!

As you can imagine, it pays to be very careful when using the DELETE statement. This is where Safe Updates mode can help (as we covered when updating our data).

As it turns out, I was only able to run the above statement after disabling Safe Updates mode. So, before I ran that statement, I ran the following command:

So now it's probably a good time for me to enable Safe Updates again — before I do any more damage…

Restore the Data

Now that we've wiped out all records from the Fruit table, let's see if we can restore them to their original value. Fortunately for us, we previously wrote a script to insert data into our tables.

  1. The Code

    So let's take out the bit that populates the Fruit table and run that:

  2. The Result

    Table with restored data

    Now our table should look like it did earlier when we initially populated the data.