Delete duplicate rows but keep one.

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.

 

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.

 

thanks for the reply!
This query gives an error : You can't specify target table 'phones' for update in FROM clause

 

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

 

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!

 

Easiest would probably be to alter ignore take set unique key

 

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.

 

You do realize that unique keys are not limited to one field

ALTER IGNORE  TABLE `phones`
    ADD UNIQUE INDEX `uniquephones` (`phones`, `office`);

Code (markup):