MYSQL Delete all rows with the minimum DATE value-CodePudding

In MYSQL, I have a dataset which contains stock data of many many dates, and I want to delete all the rows that have the lowest date value (my date value is an integer, so in other words, I want to delete trades from the first date). I have found this code on stack overflow, but it only deletes one row, while I want to delete ALL rows with this minimum date.

This is the code I have found.

DELETE FROM TABLE 
ORDER BY DATE
LIMIT 1

Thank you!

CodePudding user response：

Use a query to find out minimum date then delete all records for this date.

-- MySQL
DELETE t
FROM TEST_TRADES t
INNER JOIN (SELECT MIN(ï»¿COB_DATE) a
              FROM TEST_TRADES) tt
        ON t.ï»¿COB_DATE = tt.a

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4262ea268d2d9ba9b617142edf536cdc

CodePudding user response：

I found solution to this question: This is what I've done: SELECT @MIN_DATE := MIN(DATE) FROM TABLE T; DELETE FROM TABLE WHERE T.DATE = @MIN_DATE