CODEWITHSUNDEEP
mysql
Alex27
Alex27

Reputation: 89

MySQL LOAD DATA with updated file

I have a query that loads data from a text file into a table, finds a row with a max value in the "date_time" column and deletes all rows that are less than the max value. But this file will be updated several times a day and each time only one row with the max value will remain in the table.

LOAD DATA INFILE 'C:/users/user/desktop/download.txt' IGNORE INTO TABLE download1
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 3 LINES;
DELETE FROM download1 WHERE date_time < (SELECT * FROM (SELECT MAX(date_time) AS MaxDatetime FROM download1) AS t)

How can I make the past max value also remain in the table when executing a query with an updated file?

text file:

text file

table:

table

Upvotes: 1

Views: 68

Answers (1)

Shadow
Shadow

Reputation: 34231

Updated based on question edit and comments.

Since the id field in the table is auto_increment, it provides a continuously increading value. Get the max value of the id field before uploading your new file and use that to limit your delete to newer records only:

SET @OLDMAXID = IFNULL((SELECT MAX(id) FROM download1), 0);
LOAD DATA INFILE 'C:/users/user/desktop/download.txt' IGNORE INTO TABLE download1
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 3 LINES;
DELETE FROM download1 WHERE date_time < (SELECT * FROM (SELECT MAX(date_time) AS MaxDatetime FROM download1) AS t) and id > @OLDMAXID;

Upvotes: 1

Related Questions