mq8

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 UPDATEstatements, the DELETE statement accepts a WHERE clause so that you can specify the exact record/s to delete.

Syntax

DELETE
FROM table_name
WHERE column_name = value;

Example

1. The Data

   

   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.

   DELETE
               FROM Fruit
               WHERE FruitId = 5;

3. 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.

1. The Code

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

   DELETE FROM Fruit;

2. 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:

SET SQL_SAFE_UPDATES = 0;

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

SET SQL_SAFE_UPDATES = 1;

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:

   INSERT INTO Fruit 
           VALUES 
           (1,'Apple',10,1,'2015-02-15 10:30:00','2015-02-15 10:30:00'),
           (2,'Orange',5,2,'2015-02-15 10:30:00','2015-02-15 10:30:00'),
           (3,'Banana',20,6,'2015-02-15 10:30:00','2015-02-15 10:30:00'),
           (4,'Watermelon',10,1,'2015-02-15 10:30:00','2015-02-15 10:30:00'),
           (5,'Grapes',15,6,'2015-02-15 10:30:00','2015-02-15 10:30:00'),
           (6,'Strawberry',12,7,'2015-02-15 10:30:00','2015-02-15 10:30:00');

2. The Result

   

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