Answered by:
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
Saturday, September 3, 2016 2:18 PM
Answers
-
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- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 5, 2016 12:53 AM -
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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 5, 2016 5:52 AM
All replies
-
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- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 5, 2016 12:53 AM -
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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, September 5, 2016 5:52 AM