How to delete rows from a table with MySQL DELETE
MySQL DELETE lets you delete entries from a table. You can use the command to remove one or all rows from the table.
What is MySQL DELETE?
MySQL works with tables and helps you to structure and store your data. To make sure that the database management system and stored entries are always up to date, data must be deleted from time to time. The command to use for this is MySQL DELETE. This function lets you specify exactly which data record should be deleted as well as add a note.
MySQL DELETE syntax
The basic syntax of MySQL DELETE is simple. Once you learn MySQL, then you’ll become familiar with commands like MySQL CONCAT, MySQL REPLACE, MySQL DATE or MySQL DATEDIFF. For MySQL DELETE, you need to provide the system with two essential pieces of information. It looks like this:
DELETE FROM table
WHERE condition;“FROM” tells the system from which table the record should be deleted. With “WHERE” you specify which condition a record must fulfill to be removed. If you omit the “WHERE” condition, the entire row is deleted.
MySQL DELETE example
How exactly MySQL DELETE works is best explained with a practical example. For this, we’ll imagine a company with a customer directory. This is stored in a table called “Customers”.
Customer number | Name | City | Country |
1 | Jones | London | United Kingdom |
2 | Smith | Toronto | Canada |
3 | Johnson | Berlin | Germany |
4 | Williams | New York | United States |
5 | Brown | Los Angeles | United States |
If we want to delete the customer “Jones”, you can use the MySQL DELETE command. The following is entered:
DELETE FROM Customers
WHERE Name = "Jones";The row will then be deleted from the table.
Delete all rows in MySQL DELETE
As mentioned above, you can also use the MySQL DELETE command to delete all rows from a table, but don’t want to delete the table itself. The command matching the above example looks like this:
DELETE FROM Customers;This removes all entries while the table and its structure remain intact.
MySQL DELETE LIMIT
The addition “LIMIT” limits the number of rows to be deleted with MySQL DELETE. To do this, you first tell the system to sort the entries. The basic syntax looks like this:
DELETE FROM table
ORDER BY: condition
LIMIT: number;For an example like the customer list above, you could use the MySQL DELETE command with the “LIMIT” addition like this:
DELETE FROM Customers
ORDER BY Name
LIMIT 2;In this case, the system sorts all customers alphabetically by the first letter of their last names and then deletes the first two.
An alternative use of MySQL DELETE with “LIMIT” looks like this:
DELETE FROM Customers
WHERE Country = “United States”
ORDER BY Name
LIMIT 2;In this case, all customers from the United States would be sorted alphabetically and the first two entries would be deleted.
Related articles
How to install MySQL on Ubuntu 22.04
MySQL is a useful application on Ubuntu and it is a standard component in the LAMP stack. In our article, you will learn how to install MySQL on Ubuntu 22.04 step by step and configure it for your specific needs. Once the setup and settings configuration are complete, nothing will get in the way of your daily database administration tasks.
How to install MySQL on Ubuntu 20.04
The well-known open-source database MySQL is used on various platforms. When you combine MySQL with Ubuntu 20.04, you get a robust and efficient database environment for a variety of use cases, from web hosting to enterprise. In this article, we’ll show you step by step how to install MySQL on Ubuntu 20.04 and which requirements your system should fulfill.
