Delete duplicate records in MYSQL

Question

Delete duplicate records in MYSQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm using a command that runs every second on node.js. It has the function of excluding any duplicate records for a given 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 bidding_price column, and keep only one. But it is important that he does this search not across the table, but rather for a certain item as I reported at the beginning, through the auction_id column.

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 bid_account table has the id column as the 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

php mysql sql

asked by anonymous 14.04.2018 / 21:14

2 answers

0

It turns out that MySQL does not allow aliases in the DELETE statement, you have to execute 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;

No sub-select you can call normally.

15.04.2018 / 03:48

APPLICATION VB6 ERROR App with Cordova / Ionic with backend in NDK

score 0 · Accepted Answer

I was able to resolve it as follows:

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