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.
Syntax
Example
-
The Data
Here's the data before we delete anything.
-
Delete some Data
Let's delete red grapes from our list of fruit. Red grapes have been assigned a FruitId of
5so we will use that in ourDELETEstatement. -
The Result
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.
-
The Code
To delete all records in a table, just omit the
WHEREclause. Like this: -
The Result
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.
-
The Code
So let's take out the bit that populates the Fruit table and run that:
-
The Result
Now our table should look like it did earlier when we initially populated the data.