Mysql select from another table in a load infile

Question

I have a csv file that I am loading in to a mysql db using the load local infile command.

The csv has a set of fields that are gathered from an enum of 5 values and I will need to query based on these. To speed up the queries (possibly up to 6 million rows) I'm trying to select on a reference table to insert an id rather than the string value.

load data local infile 'test_file.csv'
    into table matching
    fields terminated by ','
    enclosed by '"'
    (... @match, ...)
    set
        ...
        match = select id from matchRefData where name = @match,
        ...;

According to http://dev.mysql.com/doc/refman/5.1/en/load-data.html subqueries can be used in the right side of the set operation but it seems to fail every time with:

ERROR 1064 (42000) at line 1: 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 'select id from matchRefData where name = @match,...'

Version information:

Server version: 5.5.25 Source distribution

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.1.8               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.25              |
| version_comment         | Source distribution |
| version_compile_machine | i386                |
| version_compile_os      | osx10.7             |
+-------------------------+---------------------+

score 3 · Answer 1 · answered Oct 12 '12 at 13:36

3

Try this query -

LOAD DATA LOCAL INFILE 'test_file.csv'
  INTO TABLE matching
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  (..., @match, ...)
  SET `match` = (SELECT id FROM matchRefData WHERE name = @match)

answered Oct 12 '12 at 13:36

Devart

119,203
23
166
186

Mysql select from another table in a load infile

1 Answers1