CODEWITHSUNDEEP
sqlms-access
user3045856
user3045856

Reputation:

Removing duplicate rows in Access

I've got a table with duplicates in M$ Access, like:

      NAME 
----------------
Charlie
Anderson Silva
Minotauro
Cigano
Sonnen
Charlie
Charlie
Minotauro
Anderson Silva

How could I delete the duplicates on this table and leave only each unique name?

Something like:

      NAME 
----------------
Charlie
Anderson Silva
Minotauro
Cigano
Sonnen

Is just about using a SELECT inside a a DELETE that Groups By "NAME"? If yes, how would it be? Thanks!

Upvotes: 0

Views: 88

Answers (1)

Squirrel5853
Squirrel5853

Reputation: 2406

SELECT
    MIN(ID) AS ID, Name
FROM
    TableName
GROUP BY 
    Name

This will give the smallest ID of each of the names

1 | Charlie   |
2 | Anderson  |
3 | Mino      |

etc

so then you can just 

DELETE TableName WHERE ID NOT IN
(
     SELECT
        MIN(ID) AS ID
    FROM
        TableName
    GROUP BY 
        Name
)

Upvotes: 1

Related Questions