Delete records with multiple conditions

Question

I need to delete duplicate entries in the column bidding_price with the following conditions:

Table: bid_account

Columns to be checked:

id = PRIMARY KEY
auction_id = ID of each product
bidding_price = inserted value (to be verified duplicity for each product)
bid_flag = should always be the same to the value of: 'd'
bidding_type = should always be the same as value of: 's'

It will always exist equal records in the column bidding_price, what you cannot have is equal records with the same product ID (auction_id).

Example of how it should not have:

auction_id | bidding_price
------10-0-0.02
------10-0-0.02
------11--------------0.02
------11--------------0.02

The right thing would be:

auction_id | bidding_price
------10-0-0.02
------11--------------0.02

I tried with the following command:

DELETE ba
    FROM bid_account ba JOIN
         (SELECT ba2.auction_id, ba2.bidding_price, MAX(ba2.id) as max_id
          FROM bid_account ba2
          WHERE ba2.bid_flag = 'd' AND ba2.bidding_type = 's'
          GROUP BY ba2.auction_id, ba2.bidding_price
         ) ba2
         ON ba2.auction_id = ba.auction_id AND
            ba2.bidding_price = ba.bidding_price AND
            ba2.max_id < ba.id
WHERE ba.bid_flag = 'd' AND ba.bidding_type = 's' AND ba.auction_id = ba2.auction_id

Only it deleted several records, did not validate correctly. How should I do?

Answer 1

DELETE ba1 FROM bid_account ba1, bid_account ba2, 
         WHERE ba1.auction_id = ba2.auction_id AND
             ba1.bidding_price = ba2.bidding_price AND
             ba1.max_id < ba2.id AND
                 ba1.bid_flag = 'd' AND 
                 ba1.bidding_type = 's';