Scribd
Upload
2 views5 pages

Delete Data From a MySQL Database

The document explains how to delete data from a MySQL database using the SQL DELETE statement, including syntax and examples for deleting specific records and all records from a table. It emphasizes the importance of being cautious when using the DELETE statement and mentions the use of Safe Updates mode. Additionally, it provides instructions on restoring deleted data by re-inserting original values into the table.

Uploaded by

khadija akhtar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views5 pages

Delete Data From a MySQL Database

The document explains how to delete data from a MySQL database using the SQL DELETE statement, including syntax and examples for deleting specific records and all records from a table. It emphasizes the importance of being cautious when using the DELETE statement and mentions the use of Safe Updates mode. Additionally, it provides instructions on restoring deleted data by re-inserting original values into the table.

Uploaded by

khadija akhtar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

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

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.

You might also like