mysql load data infile where clause

mysql load-data-infile
user157195 picture user157195 · Jul 21, 2010 · Viewed 14.9k times · Source

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

Ondřej Hlaváček picture Ondřej Hlaváček · Mar 23, 2018

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;

Related questions

How do I import CSV file into a MySQL table?

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column …

Read More
MYSQL import data from csv using LOAD DATA INFILE

I am importing some data of 20000 rows from a CSV file into Mysql. Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns? When I execute LOAD …

Read More
Import CSV to mysql table

What is the best/fastest way to upload a csv file into a mysql table? I would like for the first row of data be used as the column names. Found this: How to import CSV file to MySQL table …

Read More