1. There is going to be scheduled downtime in the next few hours.

    We are physically moving servers and equipment to a data center that will give us 50x more bandwidth.
  2. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Delete duplicate rows but keep one.

Discussion in 'Databases' started by bumbar, Sep 27, 2016.

  1. #1
    Hello,
    I want to delete only the repeated records that belong to the office. Should remain one of the greatest ID. Repeated phones with different offitseId must remain.

    Phone table:
    id officeId phone
    -----------------------
    1 10 111
    2 11 222
    3 11 222
    4 12 111
    5 13 444
    6 14 555
    7 14 555

    Below is the query that deletes all repeated phone without complies with offiseId condition.
    
    delete phones
       from phones
      inner join (
         select max(id) as lastId, phone
           from phones
          group by phone
         having count(*) > 1) duplic on duplic.phone = phones.phone
      where phones.id < duplic.lastId
    ;
    Code (markup):
    For example, after the query run in the table must be deleted records with IDs 2 and 6, phones 222 and 555. However, it must remain phones ID 1 and 4 with phones 111 and 111 because they are different officeId.
    I would be grateful if someone help.
    Thanx.
    SEMrush
     
    Last edited: Sep 28, 2016
    bumbar, Sep 27, 2016 IP
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,644
    Likes Received:
    212
    Best Answers:
    18
    Trophy Points:
    330
    #2
    You're probably going to need to use a nested query to identify the highest ID and delete the ones which are not the highest.

    Just conceptual here off the top of my head:

    DELETE FROM phones WHERE id NOT IN (
    SELECT id FROM phones
    GROUP BY officeId
    ORDER BY id DESC
    )

    I choose not to use count(*) or an inner join because I only need to identify the max id for each officeId and can discard everything else.

    Make sure to do a backup and test before committing since this is irreversible and has the potential to remove a lot of data from the table.
     
    jestep, Sep 28, 2016 IP
  3. bumbar

    bumbar Member

    Messages:
    66
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #3
    thanks for the reply!
    This query gives an error : You can't specify target table 'phones' for update in FROM clause
     
    bumbar, Sep 28, 2016 IP
  4. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,644
    Likes Received:
    212
    Best Answers:
    18
    Trophy Points:
    330
    #4
    Dang it you're right. I forgot you can't perform an update / delete form the same table that the nested query is operating on.

    See this post, you should be able to figure out how to modify it for your table. You'll basically want to join the table to itself instead of doing nested query. http://stackoverflow.com/questions/3346068/delete-statement-in-a-same-table
     
    jestep, Sep 29, 2016 IP
  5. bumbar

    bumbar Member

    Messages:
    66
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #5
    This is the decision
    
    DELETE FROM phones WHERE id NOT IN (
        SELECT * FROM (SELECT id FROM phones
            GROUP BY officeId
            ORDER BY id DESC
        ) as X
    )
    
    Code (markup):
    Perhaps there are other solutions, yet it works!
    Thank you again!
     
    bumbar, Sep 30, 2016 IP
  6. Einheijar

    Einheijar Well-Known Member

    Messages:
    537
    Likes Received:
    13
    Best Answers:
    3
    Trophy Points:
    115
    #6
    Easiest would probably be to alter ignore take set unique key
     
    Einheijar, Oct 4, 2016 IP
  7. bumbar

    bumbar Member

    Messages:
    66
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #7
    if it makes a unique key on their phones, not possible at two different offices to share the same phone. This eliminated as an option.
     
    bumbar, Oct 7, 2016 IP
  8. Einheijar

    Einheijar Well-Known Member

    Messages:
    537
    Likes Received:
    13
    Best Answers:
    3
    Trophy Points:
    115
    #8
    You do realize that unique keys are not limited to one field
    
    ALTER IGNORE  TABLE `phones`
        ADD UNIQUE INDEX `uniquephones` (`phones`, `office`);
    
    Code (markup):
     
    Einheijar, Oct 7, 2016 IP