If you have a table in your Database and there is a duplicate values in it How we can delete one raw of each duplicated rows?

There are many ways to delete duplicated values from database, but I have chosen different way to delete duplcated values. 

fisrt you will max the duplicated column and group also the duplicated column and using the cluse( having count(*) > 1) in this case you will get only that duplicated values, then you will need to make a sub-query to show the max(rowid) and duplicated column group by duplicated column with considering naming the rowid as you prefer. then you will select the rowid from another sub-query, then preform the DML statement DELETE table where rowid in the above values appear. 

The below script is an example of what I am explaning above:

 

DELETE DUPLICATED_VALUES

WHERE ROWID IN (

SELECT ROWID_ALIAS

FROM (

SELECT MAX(ROWID) AS ROWID_ALIAS , ID

FROM DUPLICATED_VALUES WHERE ID IN (

SELECT MAX(ID)

FROM DUPLICATED_VALUES

GROUP BY ID

HAVING COUNT(*) >1

)

GROUP BY ID

)

)

 

 

In this case the above script will delete only one row of each duplicated rows. 

Insert the distinct rows from the duplicate rows table to new temporary table. Then delete all the data from duplicate rows table after that insert all data from the temporary table that has no duplicates.

select distinct * into #temp from table_name

delete from table_name

insert into table_name

select * from #temp

 

drop table_name

 

if you add a primary key to the table that prevents storing duplicate rows in the first place.

Depending on which row you want to keep i.e min (rowid) or max (rowid), you can delete the duplicate rows using group by clause on columns that has duplicates. Below an example to delete min (rowid):

delete from table_name where rowid not in 

select max(rowid)

from table_name

group by col1, col2, .....);

 

 

Microsoft SQL Server tables should never contain duplicate rows, nor non-unique primary key. refere to:https://support.microsoft.com/en-us/help/139444/how-to-remove-duplicate-rows-from-a-table-in-sql-server

 

use Common Table Expression to get rid of duplicates.

eg.

WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS rid FROM MyTable ) DELETE FROM CTE WHERE rid<>1

To ensure that you will delete all the duplicated values, here are the steps:

1. Backup the table first so you can retrieve whenever you executed a wrong query - SELECT * INTO TABLEBACKUP FROM MAINTABLE

2. SELECT DISTINCT(DUPLICATEDROW) INTO NEWTABLE

3.DROP MAINTABLE - if you already checked the newtable

 

 

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

Get all diplicate rows into temp Delete all diplicate rows from original table Filter temp and insert into original table from temp

By Using DISTINCT Keyword at SQL, We can Eliminate Duplicate Values Of Mentioned Row... 

1- open transaction.

2- Query and Save the dublicated rows into temporary table.

3- delete the dublicated rows from he original table.

4-Insert the dublicated rows to original table from temporary table .

5- drop the temporay table.

6- Commit.

 

DELETE FROM table_name where col_1 NOT IN (SELECT max(col_1) FROM table_name GROUP BY col_2)