Deleting Duplicate DB table with lower ID

[jamescalvin]

Hey All,

 

I'm trying to delete a duplicate table in my DB.  However, my real problem is this, I need to delete the duplicate table with the lowest ID (and keep the one with the highest ID).  Can someone shed some light on me about how to do this?

 

Thanks in advance

[effigy]

Do you mean row, not table?

Could you explain the situation a little further?

Are there relationships you need to move around?

 

Without more information, it sounds like you just want to select either a COUNT(*) or a MAX(id) on each table, and, based on this result, run a DROP table.

[artacus]

You want to delete duplicate records I take it. Run the subselect first to make sure its doing what you want.

DELETE 
myTable
FROM myTable
JOIN (
    SELECT a.id
    FROM myTable AS a
    JOIN myTable AS b ON a.dupInfo = b.dupInfo AND a.id < b.id
) AS sub ON myTable.id = sub.id

[jamescalvin]

Sorry about that, I did mean duplicate rows.  I'm a newbie at this...  My DB language is waaaay immature so please bare with me while I try re-explaining this one. 

 

I have a table with about a thousand rows.  A lot of the rows are duplicates.  I have a column called CID and the number structure for that is xxxxx-xx

 

I'm trying to delete all of the duplicates and keep the one with the highest CID.  If I change all of the "id" to "cid" in the following code, will I be good to go?

 

DELETE 
myTable
FROM myTable
JOIN (
    SELECT a.id
    FROM myTable AS a
    JOIN myTable AS b ON a.dupInfo = b.dupInfo AND a.id < b.id
) AS sub ON myTable.id = sub.id

[artacus]

Let me explain my pseudo code.

 

First you have to join the table back on itself on whatever determines that it is a duplicate. So that might be something unique like driver license, or email address. That will return all of our id's (CID) that have a matching row with a higher CID. Those CID's are the ones that we want to delete so we use the subquery to return those and the outer query to do the actual deleting.

[jamescalvin]

Sounds good, I'll give it a go and see what I come up with.  Thanks for the help all.