SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete Duplicate records in a table

    Hi
    Im using the below Query to find duplicates in a table
    which is working fine

    but now Im trying to delete all duplicates leaving just one of each record, so if theres two records the same, delete one of them
    any tips?

    Code:
    select Invoice_Number,Company_number,count(*) as n
     from `TEST`.`invoice`
    group by Invoice_Number
    having  n > 1

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does it matter which invoice_number is kept for each company?

    If not you can run

    Code:
    ALTER IGNORE TABLE
    ADD UNIQUE (company_number, invoice_number)
    that will leave only one invoice_number for each company_number.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    dude!!! omg!!! it's completely the opposite!!!

    what you should really be aiming for is one company for each invoice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The duplicate record has the same company number,
    so for example company 1 has an invoice number 12345
    this invoice (12345) is in the table twice with the same company (1)
    so i need to delete one of those whole records
    company 2 is in the table three times with an invoice number of 5678
    so i need to get rid of two of those records

    hope that makes some sence

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,085
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by damo2009 View Post
    company 2 is in the table three times with an invoice number of 5678
    so i need to get rid of two of those records
    Any preference as to which n-1 records of n records should be deleted? Or are all records the same and should just deleting n-1 random records out of n do the job?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by damo2009 View Post
    hope that makes some sence
    yes, it did the first time

    did you try guelphdad's solution?

    of course, you backed up your table first, right?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Any preference as to which n-1 records of n records should be deleted? Or are all records the same and should just deleting n-1 random records out of n do the job?
    yep deleting random records will do the job, each set of duplicates are the exact same

    I tried your above solution but it wasnt working for me

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you'd have to explain what didn't work then because you either haven't described the problem accurately or you have implemented the solution incorrectly, probably because there were more columns involved than described.

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    you'd have to explain what didn't work then because you either haven't described the problem accurately or you have implemented the solution incorrectly, probably because there were more columns involved than described.
    Ah that was it, I had more than just them columns in the table, so when i added the others it worked fine for me

    Thanks guelphdad & r937


Bookmarks

Posting Permissions

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