MySQL LOAD DATA with updated file

Question

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:

table:

You want to keep the record(s) which have the max(date_time) for every load? — P.Salmon
– P.Salmon, Commented Jul 7, 2020 at 8:23
Yes. I want to keep the records which have the max(date_time) for every load. — Alex27
– Alex27, Commented Jul 7, 2020 at 8:27
Does the load file keep getting added to, what does download1 table look like, what does the data file look like, are you able to amend the download1 table. Too many unknowns to provide an answer. — P.Salmon
– P.Salmon, Commented Jul 7, 2020 at 8:36
I edited the question. Added what the file and table look like. The file has a limit on the number of lines, that is, when updating, the data in it is overwritten. — Alex27
– Alex27, Commented Jul 7, 2020 at 9:05

Shadow · Accepted Answer · 2020-07-08 07:18:41Z

1

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;

edited Jul 8, 2020 at 7:18

answered Jul 7, 2020 at 6:27

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alex27 Over a year ago

The query saves the row with the maximum value in the table, but when I try to execute the query with the updated file, it just inserts all the rows from the file, not just the new row with the max value.

Shadow Over a year ago

It is supposed to insert all lines, the delete removes the ones you do not need. If the new delete does not remove the lines you want to, then the assumtion I made at the beginning of my answer may not be correct. Have you checked that?

Alex27 Over a year ago

I think your assumption is correct. Query works first time, but when file updated, it not "removes the ones you do not need" (lines that less that line with max value). It just insert all lines from file.

Shadow Over a year ago

Then you need to check what value @OLDMAXDATE has.

Shadow Over a year ago

The timestamp in your table is the same as your max timestamp in your csv file, this is why the max(date_time) technique does not work. I noticed that there is an id field in the table. Is that an auto increment field?

|

Collectives™ on Stack Overflow

MySQL LOAD DATA with updated file

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related