0

I'm trying to do an update in MySQL using a local CSV file, am convinced that my syntax should be right, but am obviously missing something since MySQL tells me:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY ',' ENCLOSED BY '"' ( ArtikelNummer, Tags ) LINES TERMINA' at line 3

The code I use is the following:

CREATE TEMPORARY TABLE TempTable LIKE Assortiment;

LOAD DATA  LOCAL INFILE 'C:\\Users\\Website\\website data\\stock.csv'
    INTO TABLE TempTable ( ArtikelNo, Tags )
    FIELDS TERMINATED BY ','
           ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

UPDATE assortiment
    INNER JOIN TempTable
         ON TempTable.ArtikelNo = assortiment.ArtikelNo
         SET assortiment.Tags = TempTable.Tags;

DROP TEMPORARY TABLE TempTable;

The target table uses a lot of columns, the csv file only 2

I'm think the error is in the column specification ( ArtikelNo, Tags ), because when I leave that away there is no syntax error anymore (only a wrong mapping). I tried putting this at different locations, made sure the column names are correct, but that didn't help.

Obviously a work around would be to make sure the columns of the CSV are in the right order and put in some dummy columns where needed, but I want to find out why this goes wrong.

I'm using MySQL query browser, version 5.1.11, Using server version 5.5.20.

David Eggman
  • 38
  • 4
  • The columns list in `()` goes after the `FIELDS/LINES` clauses. http://dev.mysql.com/doc/refman/5.1/en/load-data.html – Michael Berkowski Nov 29 '13 at 21:46
  • possible duplicate of [LOAD DATA INFILE error 1064](http://stackoverflow.com/questions/19819206/load-data-infile-error-1064) -- that question from earlier this month has the exact same type of syntax error, naming the columns in the wrong place in the statement. Where did you (and possibly the author of the other question) learn the wrong syntax? Is there a mistake in the manual or in some web tutorial somewhere? – Bill Karwin Nov 29 '13 at 21:50
  • @BillKarwin You are right about the duplicate, and Michael Berkowski on what I did wrong. I didn't get the answer from a wrong manual, but from an answer on stackoverflow: [link](http://stackoverflow.com/questions/10253605/import-csv-to-update-only-one-column-in-table) That showed me how to do the import, but since I needed to also specify the lines termination I somehow wrongly figured that the column specification should go before that. Somehow I got confused by the documentation and simpler examples. I guess I'm a bit too tired. Sorry, I should have taken a better look at the manual! – David Eggman Nov 29 '13 at 22:10
  • Your mistake is natural, because the syntax for `LOAD DATA INFILE` has an `INTO ` clause, just like `INSERT`. In the syntax for `INSERT`, the columns follow immediately after that table clause, but for some reason `LOAD DATA INFILE` puts the columns clause much later in the syntax. Go figure! – Bill Karwin Nov 29 '13 at 22:17

0 Answers0