Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Delete Duplicate records

    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
    Inspiration Through Fermentation

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Delete Duplicate records

    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!

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Re: Delete Duplicate records

    Originally posted by andrewst
    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.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Delete Duplicate records

    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
    );

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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!
    Inspiration Through Fermentation

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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!

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That did the trick.
    Thanks again!
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •