locked
How to delete duplicate rows with sql query RRS feed

  • 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