It is obvious that if somehow duplicate rows get inserted into a table, it becomes a major issue to delete those duplicate rows. So this topic will help us to delete those duplicate rows from the specific table.
I used some basic T-SQL (Transact-SQL*) code to accomplish the problem. So you don’t need to worry understand the codes and even you can use just blindly.
I had a database table with duplicate rows and I wanted to get rid of it. So this is how I solved my issue.
First, say I have a table named Stores with following column.
So my first select query:
SELECT * FROM Stores
Now look carefully in above figure, Stores table does contain duplicate records. Check first two columns marked with rad box. Every the columns are the same except Id.
Now let’s find out duplicate rows in a way so that we can easily understand. We are now going to add an extra column in a temporary table, let’s name it RowNumber. RowNumber will hold count of duplicate rows.
So let’s execute the following query:
WITH Temp AS ( SELECT row_number() OVER (PARTITION BY Slno ,Region ,Territory ,Town ,ShopName ,ShopAddress ,ShopType ,Slab ,Phone ,DmsCode ,Latitude ,Longitude ORDER BY DmsCode) AS RowNumber, * FROM Stores )SELECT * FROM Temp
You see, I haven’t include Id, since it’s not duplicate.
Now if you look above table RowNumber column, you will able to know which records contain duplicate values on rows with RowNumber greater than 1.
Now if we want to keep unique values in the table and delete others execute following query:
WITH Temp AS ( SELECT row_number() OVER (PARTITION BY Slno ,Region ,Territory ,Town ,ShopName ,ShopAddress ,ShopType ,Slab ,Phone ,DmsCode ,Latitude ,Longitude ORDER BY DmsCode) AS RowNumber, * FROM nps_new.dbo.Stores ) DELETE from Temp WHERE RowNumber >1 SELECT * FROM Stores ORDER BY DmsCode
If the query executes, we will find only unique values in the table.
I did not explain the queries as I assumed that people who deals with such thing already know SQL.