How do I remove duplicates from a table?

If you have a primary key in the table (let's call it PK_Column), you can do something like this with a sub-query: 
 

CREATE TABLE dbo.Dupes  (      PK_Column INT IDENTITY(1,1),      Dupe_String VARCHAR(32)  )  GO    SET NOCOUNT ON  GO    INSERT dbo.Dupes(Dupe_String)      SELECT 'foo'      UNION ALL SELECT 'foo'      UNION ALL SELECT 'foo'      UNION ALL SELECT 'bar'      UNION ALL SELECT 'bar'      UNION ALL SELECT 'splunge'      UNION ALL SELECT 'splunge'      UNION ALL SELECT 'mort'  GO    SELECT * FROM dbo.Dupes  GO    DELETE dbo.Dupes      FROM dbo.Dupes d      LEFT OUTER JOIN       (          SELECT Dupe_String, pk_column = MIN(PK_Column)          FROM dbo.Dupes          GROUP BY Dupe_String      ) x      ON d.pk_column = x.pk_column      WHERE x.pk_column IS NULL  GO    SELECT * FROM dbo.Dupes  GO    DROP TABLE dbo.Dupes  GO

 
There are also some platform-specific ways to handle this (for example, if you don't have a primary key): 
 
SQL Server 
 
There is a straightforward method outlined in KB #139444. 
 
Another approach might be to create a second table with a primary key or unique constraint on that column, and insert into the new table all the rows from the old table. For each unique value, the first insert will succeed but all subsequents will fail due to violation of key or unique constraint. Once you're done, you can rename the old table to something else, and rename the new table with the old name. 
 
Finally, see this article for some other ideas. 
 
Access 
 
There is a really ugly kludge in KB #209573 -- requiring manual labor. 
 

---

Once you've removed the duplicate rows, please do yourself a favor and make it so that it doesn't happen again. You can see some techniques for preventing duplicates in Article #2509.