Friday, September 22, 2023

Delete Duplicate Rows

 Let’s create a sample Employee table and insert a few records in it.

In the table, we have a few duplicate records, and we need to remove them.

SQL delete duplicate Rows using Group By and having clause

In this method, we use the SQL GROUP BY clause to identify the duplicate rows. The Group By clause groups data as per the defined columns and we can use the COUNT function to check the occurrence of a row.

For example, execute the following query, and we get those records having occurrence greater than 1 in the Employee table.

Sample data

In the output above, we have two duplicate records with ID 1 and 3.

  • Emp ID 1 has two occurrences in the Employee table
  • Emp ID 3 has three occurrences in the Employee table

We require to keep a single row and remove the duplicate rows. We need to remove only duplicate rows from the table. For example, the EmpID 1 appears two times in the table. We want to remove only one occurrence of it.

We use the SQL MAX function to calculate the max id of each data row.

In the following screenshot, we can see that the above Select statement excludes the Max id of each duplicate row and we get only the minimum ID value.

identify duplicate dataTo remove this data, replace the first Select with the SQL delete statement as per the following query.

Once you execute the delete statement, perform a select on an Employee table, and we get the following records that do not contain duplicate rows.

SQL Delete duplicate rows

No comments:

Followers

Link