CODEWITHSUNDEEP
mysql
Cronin O'M
Cronin O'M

Reputation: 692

Removing duplicate entries in a MySQL database

I have a table called 'prices with the following structure: 'id', 'price, 'availability_id' and 'rate_id'. When the user made changes new records were written rather than updating existing records. What I need to do is remove duplicate records with matching 'availability_id' and 'rate_id', keeping only the record with the highest 'id'.

I have managed to list the duplicates using the following MySQL:

select   'id',
`availability_id`,
         `rate_id`,
         count(*)
from     prices
group by 'id', 
`availability_id`,
         `rate_id`
having   count(*) > 1

I would really appreciate any advice on removing these duplicates.

Upvotes: 1

Views: 174

Answers (1)

barsju
barsju

Reputation: 4446

How about something like this:

DELETE p1 FROM prices p1, prices p2 
WHERE p1.id < p2.id 
AND p1.availability_id = p2.availability_id 
AND p1.rate_id = p2.rate_id

Upvotes: 1

Related Questions