Introduction Show
There are several instances in which you may encounter duplicate rows in your MySQL database. This guide will walk you through the process of how to remove duplicate row values in MySQL. Prerequisites
Setting Up Test DatabaseIf you already have a MySQL database to work on, skip ahead to the next section. Otherwise, open a terminal window and type in the following:
When prompted, enter the root password for your MySQL installation. If you have a specific user account, use those credentials instead of root. The system prompt should change to:
Note: If you aren’t able to connect to the MySQL server, you may get the message that access has been denied. Refer to our article on how to solve this MySQL error if you need assistance. Create Test DatabaseYou can create a new table in an existing database. To do so, find the appropriate database by listing all existing instances with:
Alternatively, you can create a new database by entering the following command:
To start working in your new
Add Table and DataOnce in the database, add a table with the data below using the following command:
Display the Contents of the Dates TableTo see a display of all the dates you entered, ordered by year, type:
Display Duplicate RowsTo find out whether there are duplicate rows in the test database, use the command:
The system will display any values that are duplicates. In this case, you should see: This format works to select multiple columns. If you have a column with a unique identifier, such as an email address on a contact list or a single date column, you can simply select from that one column. Removing Duplicate RowsPrior to using any of the below-mentioned methods, remember you need to be working in an existing database. We will be using our sample database:
Option 1: Remove Duplicate Rows Using INNER JOINTo delete duplicate rows in our test MySQL table, use MySQL JOINS and enter the following:
You may also use the command from Display Duplicate Rows to verify the deletion. Note: If you have a unique column identifier, you can substitute it for the month, day, and year column identifiers, omitting the AND operators. This is designed to help you delete rows with multiple identical columns. Option 2: Remove Duplicate Rows Using an Intermediate TableYou can create an intermediate table and use it to remove duplicate rows. This is done by transferring only the unique rows to the newly created table and deleting the original one (with the remaining duplicate rows). To do so follow the instructions below. 1. Create an intermediate table that has the same structure as the source table and transfer the unique rows found in the source:
For instance, to create a copy of the structure of the sample table
2. With that done, you can delete the source table with the drop command and rename the new one:
For example:
Option 3: Remove Duplicate Rows Using ROW_NUMBER()Important: This method is only available for MySQL version 8.02 and later. Check MySQL version before attempting this method. Another way to delete duplicate rows is with the
Therefore, the command for our sample table would be:
The results include a row_number column. The data is partitioned by id and within each partition there are unique row numbers. Unique values are labeled with row number 1, while duplicates are 2, 3, and so on. Therefore, to remove duplicate rows, you need to delete everything except the ones marked with 1. This is done by running a To delete duplicate rows run:
In our example dates table, the command would be:
The output will tell you how many rows have been affected, that is, how many duplicate rows have been deleted. You can verify there are no duplicate rows by running:
For instance:
Conclusion You should now be able to remove duplicate rows in MySQL and improve your database performance. Remember, new implementations of MySQL strict mode have limited the functionality of the required functions. Which command is used to delete the duplicates?The ROW NUMBER() function can be used in connection with a common table expression (CTE) to sort the data and then remove the duplicate records.
Which keyword is used to remove duplicate rows in the result set?The DISTINCT keyword eliminates duplicate rows from a result.
How remove duplicates excel?In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, click Data > Sort & Filter > Advanced. To remove duplicate values, click Data > Data Tools > Remove Duplicates.
|