MYSQL deletes duplicate records and retains only the maximum ID

Table:productprice

Table Column:id, Productasin ...

The same productasin only retains the most recent one, that is, only the maximum ID is reserved.

First step: Detect duplicate records and maximum IDs

SELECT Productasin,max (ID) maxid
From Productprice GROUP by Productasin have COUNT (ID) >= 2)
Tempt

Step two: Isolate the record in duplicate and less than the maximum ID

SELECT productprice.id as ID from
Productprice Productprice, (SELECT Productasin,max (ID) maxid
From Productprice GROUP by Productasin have COUNT (ID) >= 2)
Tempt
WHERE Productprice.productasin = Tempt.productasin
and Productprice.id < Tempt.maxid

Step Three: Delete

DELETE from Productprice WHERE ID in (
SELECT Tempout.id from
(
SELECT productprice.id as ID from
Productprice Productprice, (SELECT Productasin,max (ID) maxid
From Productprice GROUP by Productasin have COUNT (ID) >= 2)
Tempt
WHERE Productprice.productasin = Tempt.productasin
and Productprice.id < TEMPT.MAXID) Tempout)
;

The temp table tempout is to prevent MySQL ERROR:

MYSQL Error:error code:1093. You can ' t specify target table ' Productprice ' for update in FROM clause

Example:productprice table The same day, the same productasin only the one with the largest ID

DELETE from Productprice WHERE ID in (
SELECT Tempout.id from
(
SELECT productprice.id as ID from
Productprice Productprice, (SELECT productasin,date_format (createddate, '%y-%m-%d ') as Tempdate,max (ID) maxId
From Productprice GROUP by Date_format (CreatedDate, '%y-%m-%d '), Productasin have COUNT (ID) >= 2)
Tempt
WHERE Productprice.productasin = Tempt.productasin and Date_format (productprice.createddate, '%Y-%m-%d ') = Tempt.tempdate
and Productprice.id < TEMPT.MAXID) tempout)
;

MYSQL deletes duplicate records and retains only the maximum ID