Jump to content

Deleting Duplicate DB table with lower ID


jamescalvin

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.