Subqueries with Load Data Infile

Question

I am having a problem with a particular sub query:

LOAD DATA INFILE 'some_address' INTO TABLE 'some_table' 
FIELDS TERMINATED BY 'field_terminate' ENCLOSED BY '"' 
ESCAPED BY '\\' ('fieldX', 'fieldY'....'fieldZ') 
SET fieldZ= (SELECT Id FROM another_table WHERE Name = fieldZ)

What I basically want to achieve is something to the affect of having the field from the file replaced by it's corresponding ID which sits pretty in another table at the time of executing the query. Is this even possible?

Thanks a bunch in advance. I have looked all over the internet. No luck so far though..

Oh, with the above query I get an error basically telling me the subquery isn't scalar. It's obvious the sub query isn't using being executed for the insertion of each row, rather it's being executed for all the fieldZ's of every row combined together (therefore it's returning multiple ID's)

Answer 1

To use a column's data in the SET, you have to put it in a variable:

LOAD DATA INFILE 'some_address' INTO TABLE 'some_table' 
    FIELDS
        TERMINATED BY 'field_terminate'
        ENCLOSED BY '"' 
        ESCAPED BY '\\'
('fieldX', 'fieldY', ..., @fieldZ) 
SET fieldZ = (SELECT Id FROM another_table WHERE Name = @fieldZ)

That is assuming that another_table.Name is unique. If not, you'll need to force the subquery to return 1 row, such as by adding a LIMIT clause:

SET fieldZ = (SELECT Id FROM another_table WHERE Name = @fieldZ LIMIT 1)