Sometimes you have data in a MySQL database that you just don’t need. You can delete tables and records pretty easily. In fact, it’s almost too easy. And there’s no undo. So be careful. And backup. And then backup again. And then snapshot again, before tinkerating with anything in this article.
In this article we’ll look at using the SQL DELETE statement to delete rows in a table. To do so, we’ll follow this basic syntax, which includes a WHERE clause to narrow the scope of the DELETE by specifying which records will be removed:
DELETE FROM table
WHERE column=value;
In this article, we’ll use the same “Customers” table from our first articles:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
Let’s not run this next command, but note that you can omit the WHERE statement to remove all data from a database. To do so, you would simply delete all rows in a table without deleting the table itself. I do this a lot with test databases. It leaves the schema/table structure, attributes, and indexes will be intact:
When I’m doing this, I usually specify the wildcard (*):
DELETE * FROM table;
In the following, we’re going to go ahead and remove the Microsoft record. To do so, we’ll run the DELETE and specify the table to delete data from. Then, we’ll use the WHERE to specify that we want to remove the record where the Site is Microsoft AND the Contact is Satya Nadella:
DELETE FROM Customers
WHERE Site='Microsoft' AND Contact='Satya Nadella';
The “Customers” table then looks like this:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
