mysql load data infile where clause

Question

I need to update existing rows in table with load data infile based on some condition, is this possible?

load data infile 'E:/xxx.csv'
into table tld_tod
@aaa, @xxx_date, @ccc
fields terminated by ','
 LINES TERMINATED BY '\r\n'
set xxx = str_to_date(@xxx_date, '%d-%b-%y')
where xxx is not null and aaa=@aaa 

Answer 1

You ca also create a staging table, insert the data from the CSV file into the staging table and then finally insert the data into your target table with the required operations and filtering.

CREATE TEMPORARY TABLE staging LIKE tld_tod;

LOAD DATA INFILE 'E:/xxx.csv'
INTO TABLE staging
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

INSERT INTO tld_tod
SELECT STR_TO_DATE(col_date, '%d-%b-%y') AS date
WHERE col_date IS NOT NULL;

Answer 2

In MySQL it's possible to create triggers before update. So in this case I suggest to use:

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON table
       FOR EACH ROW
       BEGIN
           IF NEW.xxx IS NOT NULL THEN
               SET NEW.xxx = 0;
           END IF;
       END;//
delimiter ;

After creating trigger, you can run load data infile without WHERE. I'm not sure what's your specific required condition, but do it inside BEGIN and END.