CODEWITHSUNDEEP
mysql
membersound
membersound

Reputation: 86915

How to retain one row and remove duplicates in mysql?

I have a mysql table with each row having like 20 fields. Among others, it has:

table: origin, destination, date, price

Now I want to remove any rows that are duplicate regarding only one set of specific fields: origin, destination, date.

I tried:

delete from mytable where id not in 
    (select id from (
            SELECT MAX(p.id) as id from mytable p group by p.origin, p.destination, p.date
    ) x)

Problem: this retains the rows with the highest id (means: last added).

Instead I'd like to retain only the row that has the lowest price. But how?

Sidenote: I cannot add an unique index, as the table is used for mass inserts by LOAD DATA and should there not throw errors. At time of load I don't know which row is the "bestprice" one.

Also I would not want to introduce any additional or temp tables copying one to another. Just modify the existing table.

Upvotes: 1

Views: 48

Answers (2)

Blank
Blank

Reputation: 12378

Self-join solution:

delete t1
from yourtable t1
join yourtable t2
on t1.origin = t2.origin
and t1.destination = t2.destination
and t1.date = t2.date
and t1.price > t2.price

Upvotes: 1

juergen d
juergen d

Reputation: 204904

delete t1 
from mytable t1
left join 
(
    SELECT origin, destination, date, min(price) as price
    from mytable
    group by origin, destination, date
) t2 on t1.origin = t2.origin
    and t1.destination = t2.destination
    and t1.date = t2.date 
    and t1.price = t2.price
where t2.origin is null

Upvotes: 0

Related Questions