How to delete duplicate rows with sql query

Question

User1991482975 posted

Hi, i can run a query to update my sql server database and i was wondering how to do a simple query which allows me to delete my dulicate rows.

basically i have a Db name Salons, which contains many hair salon.. i don't know why, some of them are repeted...

so what i would love is to find all the salon where those fields are exactly the same and keep only one!!

Here the fileds to check :

[SalonAddress]      NVARCHAR (MAX) NULL,
    [SalonCountry]      INT            NOT NULL,
    [SalonCap]          NVARCHAR (MAX) NOT NULL,
    [SalonCity]         NVARCHAR (MAX) NOT NULL,
    [SalonTel]          NVARCHAR (MAX) NULL,
    [SalonEmail]        NVARCHAR (MAX) NULL,
    [SalonWebsite]      NVARCHAR (MAX) NULL,

how to do?

Thanks

Answer 1

User-967720686 posted

Hi, 

My first suggestion would be to fix your code but if you cant then try the queries below.

-- If Your table does not have a primary key you can use the query below or if you don't want to use a primary key
-- Not a good choice though if the table has lot of data 
;		
with cteDups As (		
Select	Row_Number() Over (Partition By SalonAddress, SalonCountry, SalonCap, SalonCity, SalonTel, SalonEmail, SalonWebSite Order By SalonCountry) As RowID 
From	Salon 
)
Delete
From	cteDups Where RowID > 1


-- Now let say you have a primary key / identity seed (I name it SalonID) in the table.  
;		
with cteDups As (		
Select	MIN(SalonID) SalonID , SalonAddress, SalonCountry, SalonCap, SalonCity, SalonTel, SalonEmail, SalonWebSite
From	Salon 
		Group by SalonAddress, SalonCountry, SalonCap, SalonCity, SalonTel, SalonEmail, SalonWebSite
Having	COUNT(*) > 1
)	
Delete	n
From	Salon n 
		Inner Join cteDups e on n.SalonAddress = e.SalonAddress 
		And n.SalonCountry = e.SalonCountry
		And n.SalonCap = e.SalonCap
		And n.SalonCity = e.SalonCity
		And n.SalonTel = e.SalonTel
		And n.SalonEmail = e.SalonEmail 
		And n.SalonWebSite = e.SalonWebSite
And		n.SalonID > e.SalonId  		

-- Test
Select	*
From	Salon 

Answer 2

User-595703101 posted

Hi grafic.web,

First of all you need to decide which columns equality define duplicate among your data rows.

After you define these column list, you can use ROW_NUMBER() function in a CTE expression with DELETE command as illustrated in SQL tutorial ROW_NUMBER to Delete Duplicate Rows in SQL Table

;with CTE as
(
Select
  rn = Row_Number() Over (Partition By Col1, Col2,... Order By Coln Desc),
  Col1, Col2, Coln
From Saloon
)
Delete From CTE 
Where rn > 1