I need to put a query together to delete duplicate values from a table.
If one field contains duplicate data, delete all of the other rows.

By adding just a 2nd field, I can get the distinct row to keep, like this:

SELECT MIN(T.PARTCODE) AS MP, MIN(T.SITE) AS MS
FROM (
SELECT partCode, site
FROM tblKickouts
WHERE (((partCode) In (SELECT partCode FROM tblKickouts As Tmp GROUP BY partCode
HAVING Count(*)>1 )))) T

Now how do I keep the 1 record returned, but delete all other records containing that partCode?

Thanks

Answers to this normally require using proprietary features of the DBMS - e.g. Oracle's ROWID. What is your DBMS?

Alternatively, you could select the rows you want and insert them into a new table. Then drop the old table and rename the new one.

Also, this is one reason why every table should have a primary or unique key - no exceptions!

Thanks for the reply.

I'm using SQL Server.

This table is populated each night as we import data from our legacy system (INFORMIX) to my database. We import multiple tables, and the same partcode kickout, (probably a new product), can occur in each of these tables. I worked with my IT person, who administrates this process, and we decided this was one case where we could not have a unique key, other than than the autonumber ID field.

OK, then you can use the autonumber ID something like this:

delete table t1
where id >
( select min(id) from table t2
where t2.partcode = t1.partcode
);

OK, now I've got it.
This got me going in the right direction, but I fear this one would have deleted all but the first record in the table.

I modified it to this:

DELETE FROM tblKickouts
WHERE tblKickouts.kickoutID IN
(SELECT TMP.kId
FROM tblKickouts INNER JOIN
(SELECT MIN(tblKickouts.kickoutID) AS kId, tblKickouts.partCode
FROM tblKickouts INNER JOIN tblKickouts tblKickouts_1 ON tblKickouts.partCode = tblKickouts_1.partCode
GROUP BY tblKickouts.partCode) TMP ON tblKickouts.partCode = TMP.partCode AND tblKickouts.kickoutID > TMP.kId)

This works, but is it overcomplicated?

Thanks for the help!

Well I think it could be simplified to this:

DELETE FROM tblKickouts
WHERE tblKickouts.kickoutID NOT IN
(SELECT MIN(tblKickouts.kickoutID) AS kId
FROM tblKickouts
GROUP BY tblKickouts.partCode)

The subquery gets you the IDs you want to keep (the lowest ID for each partCode). The main query then deletes all but those.

However, beware that NOT IN queries can sometimes be slow!

That did the trick.
Thanks again!