none
how to delete duplicate rows in access

    Question

  • hi

    i have this table:

    MEN
    ===
    Fname | Lname | Age
    ==============
    AA       | BB      | 30
    AA       | BB      | 40
    AA       | BB      | 30
    DD       | GG      | 30
    DD       | GG      | 30
    VV       | EE      | 30
    ZZ       | SS      | 30

    i need to remove the duplicate rows - but leav only one row

    like this:

    AA       | BB      | 30
    AA       | BB      | 40
    DD       | GG      | 30
    VV       | EE      | 30
    ZZ       | SS      | 30

    how i can do it ?

    Thursday, June 21, 2012 10:41 AM

Answers

  • No way to delete selectively only one of the duplicate rows. 

    Certainly there are ways to delete duplicate rows in SQL server

    create table #temp(Fname varchar(2), Lname varchar(2), Age int)
    
    insert into #temp values('AA','BB','30'),
    ('AA','BB','40'),
    ('AA','BB','30'),
    ('DD','GG','30'),
    ('DD','GG','30'),
    ('VV','EE','30'),
    ('ZZ','SS','30')
    
    with cte as (
    select * ,ROW_NUMBER()over(partition by fname,lname,age order by fname,lname,age )RN from #temp)
    delete from cte where RN>1
    
    
    select * from #temp

    But here the problem is E_Gold is using Access not SQL Server and i think your queries will work only in SQL server.

    Regards
    Satheesh

     
    Friday, June 22, 2012 4:24 PM
  • select distinct fname,lname,age from MEN

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.


    Thursday, June 21, 2012 10:56 AM
  • Hi,

    In MsAccess if you want to delete the duplicates, your option will be to insert the data into a another table using a distinct query
    and then later move the new tables content back to the original table after truncating that

    SELECT distinct fname,lname,age INTO MEN_COPY from MEN
    TRUNCATE TABLE MEN
    INSERT INTO MEN SELECT * FROM MEN_COPY


    Regads
    Satheesh

    Thursday, June 21, 2012 12:05 PM

All replies

  • In access? 

    SELECT DISTINCT  did not work?


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Thursday, June 21, 2012 10:51 AM
  • select distinct fname,lname,age from MEN

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.


    Thursday, June 21, 2012 10:56 AM
  • Hi,

    In MsAccess if you want to delete the duplicates, your option will be to insert the data into a another table using a distinct query
    and then later move the new tables content back to the original table after truncating that

    SELECT distinct fname,lname,age INTO MEN_COPY from MEN
    TRUNCATE TABLE MEN
    INSERT INTO MEN SELECT * FROM MEN_COPY


    Regads
    Satheesh

    Thursday, June 21, 2012 12:05 PM
  • Hi E_Gold,

    I would use the following query to remove the duplicate rows:

    SELECT DISTINCT Fname, Lname, Age FROM Men

    I hope that this helps.


    Lee Warren

    Friday, June 22, 2012 2:54 PM
  • No way to delete selectively onlly one or more of the duplicate rows (leaving a single version of this row). There are three ways:

    1. You should delete all the duplicate rows, then insert a deduplicated version of these rows. This is the preferred option because ir moves less data: imagine you have 20 millon rows and only a few duplicates:

    IF OBJECT_ID('tempdb.dbo.#menDup') IS NOT NULL DROP TABLE #menDup
    SELECT men.Fname ,men.Lname ,men.Age INTO #menDup FROM [MEN] men GROUP BY men.Fname ,men.Lname ,men.Age HAVING COUNT(*) > 1 DELETE men FROM [MEN] men JOIN #menDup menDup ON men.Fname = menDup.Fname AND men.Lname = menDup.Lname AND men.Age = menDup.Age INSERT [MEN] ( Fname ,Lname ,Age ) SELECT menDup.Fname ,menDup.Lname ,menDup.Age FROM #menDup menDup

    2. Recreatin the table without duplicates on another temporary table, delete the original table an then indicate the correct name of the table (less tiem without acces to the table):

    IF OBJECT_ID('MEN_temp') IS NOT NULL
    	DROP TABLE [MEN_temp]
    	
    SELECT DISTINCT
    	men.Fname
    	,men.Lname
    	,men.Age
    INTO
    	men_temp
    FROM
    	[MEN] men
    	
    DROP TABLE [MEN]	
    EXEC sp_rename 
    	@objname = 'MEN_temp' 
    	,@newname = 'MEN' 
        ,@objtype = 'OBJECT'

    3. Or as some colleagues: deduplicated store a version of the table in a temporary table, delete the original table rows (truncating o deleting) and reinsert the deduplicated data, but this option leaves long time data unavailable. And I do not like, do not write the code ;-)

    Put a Primary Key or a Unique Index!!! Is is polite in databases, if you are not a logging table without integrity (you stored few logging fields for logging or with little definition to create a primary key)


    Miguel Arroyo Pérez
    maperon@maperon.com
    +34600474222
    Friday, June 22, 2012 4:12 PM
  • No way to delete selectively only one of the duplicate rows. 

    Certainly there are ways to delete duplicate rows in SQL server

    create table #temp(Fname varchar(2), Lname varchar(2), Age int)
    
    insert into #temp values('AA','BB','30'),
    ('AA','BB','40'),
    ('AA','BB','30'),
    ('DD','GG','30'),
    ('DD','GG','30'),
    ('VV','EE','30'),
    ('ZZ','SS','30')
    
    with cte as (
    select * ,ROW_NUMBER()over(partition by fname,lname,age order by fname,lname,age )RN from #temp)
    delete from cte where RN>1
    
    
    select * from #temp

    But here the problem is E_Gold is using Access not SQL Server and i think your queries will work only in SQL server.

    Regards
    Satheesh

     
    Friday, June 22, 2012 4:24 PM
  • True, I had forgotten it was Access and not TSQL.

    Interesting way to delete those rows repeated. Thank you for share it. The CTE always have something new to teach.

    Miguel Arroyo Pérez
    maperon@maperon.com

    

    Friday, June 22, 2012 6:17 PM