SQL delete records with same ID, leaving 1

Strange question, I know. I don't want to delete all the rows and start again, but we have a development database table where some of the rows have duplicate IDs, but different values.

I want to delete all records with duplicate IDs, so I can force data integrity on the table for the new version and build relationships. At the moment it's an ID that is inserted and generated by code (legacy).

From another question I got this:

delete 
   t1 
from 
   tTable t1, tTable t2 
where 
   t1.locationName = t2.locationName and  
   t1.id > t2.id

But this won't work as the IDs are the same!

How can I delete all but one record where IDs are the same? That is, delete where the count of records with the same ID开发者_高级运维 > 1? If that's not possible, then deleting all records with duplicate IDs would be fine.

In SQL Server 2005 and above:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY locationName ORDER BY id) rn
        FROM    tTable
        )
DELETE
FROM    q
WHERE   rn > 1

Depends on your DB server, but you can associate DELETE and LIMIT (mysql) or TOP (sql server).

You could also move the first (not duplicate) of each record to a temp table, delete the original table and copy the temp table back to the original one.

Not sure for mysql but for a MSServer database you could use the following

SET IDENTITY_INSERT [tablename] ON
SELECT DISTINCT col1, col2, col3 INTO temp_[tablename] FROM [tablename]
ALTER TABLE temp_[tablename] ADD IDcol INT IDENTITY
TRUNCATE TABLE [tablename]
INSERT INTO [tablename](IDcol, col1, col2, col3) SELECT IDcol, col1, col2, col3 FROM temp_[tablename]
DROP TABLE temp_[tablename]

Hope this helps.