Delete duplicate records in MYSQL

Question

I am using a command that runs every second in Node.js. It has the function of deleting any record in duplicity for a particular item, which is specified as in the example by AND t1.auction_id = 1335.

DELETE FROM bid_account t1
WHERE t1.id < (Select max(t1.id) FROM bid_account t2 WHERE t1.bidding_price = t2.bidding_price) AND t1.auction_id = 1335;

I need it to delete a record that has an equal value in the column bidding_price, and keep only one. However it is important that he does this research not in the whole table, but for a certain item as I reported at the beginning, by the column auction_id.

I tried to run the above command, but it returns the following error:

#1064 - Você tem um erro de sintaxe no seu SQL próximo a 't1
WHERE t1.id < (Select max(t1.id) FROM bid_account t2 WHERE t1.bidding_price ' na linha 1

What’s wrong with this query?

I use the MYSQL database, and the table bid_account possessed the column id as index and primary.

If I use SELECT below, it returns the values in duplicity normally.

SELECT bidding_price, count(*) FROM bid_account WHERE `auction_id` = 1335 GROUP BY bidding_price Having Count(*) > 1

Answer 1

It turns out that the MySQL does not allow nicknames in instruction DELETE, you have to run like this:

DELETE FROM bid_account
WHERE id < (
    Select max(t2.id) FROM bid_account t2
    WHERE bidding_price = t2.bidding_price
) AND auction_id = 1335;

In the sub-select you can call it normal.

Answer 2

I was able to solve it this way:

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.auction_id = ?
          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.auction_id = ?;