load data infile with unknown system variable

I´ve been trying to use load data infile procedure to load data from a csv file to mysql, althougt I'ven been unable to due it because of a unknown variable error in the set statement.

LOAD DATA LOCAL INFILE 'D:\\Bets\\BD\\tables\\match.csv' 
INTO TABLE `bets`.`match` 
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' 
LINES TERMINATED BY '\r\n' 
(`idMatch`, `idChampionship`, @date, `homeTeam`, `awayTeam`, `homeTeamGoals`, `awayTeamGoals`, `matchType` );
SET matchDate = STR_TO_DATE(@date, 'YYYY-MM-DD')

The date field to import is in format 2011-08-07 and MySQL server 5.1.

when running the query the error given is: SQL Error (1193): Unknown system variable 'matchDate'.

any help would be pappreciated... thanks!

The LOAD command works but the second query fails, or? So, if you want to set a local session variable. Is the @date variable something you have set previously, or what is it?

Setting a local variable:

SET @date:='2011-11-11';
SET @matchDate:=STR_TO_DATE(@date, 'YYYY-MM-DD')

This is the result when I run the query in phpmyadmin:

LOAD DATA LOCAL INFILE 'D:\\Bets\\BD\\tables\\match.csv' 
INTO TABLE `bets`.`match` 
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' 
LINES TERMINATED BY '\r\n' 
(`idMatch`, `idChampionship`, @date, `homeTeam`, `awayTeam`, `homeTeamGoals`, `awayTeamGoals`, `matchType` );
# 380 rows affected.
SET @matchDate =STR_TO_DATE(@date, 'YYYY-MM-DD')
# MySQL returned an empty result set (i.e. zero rows).

date variable is in the field list and is the field of the csv with the date I want to import. How do I need to use it so I can read the data from the different rows of the file?

thanks for your help :)

if date is meant as a field of the table, omit the @ prefix; these are only used for user variables (see herehttp://dev.mysql.com/doc/refman/5.1/en/set-statement.html).

"How do I need to use it so I can read the data from the different rows of the file?" you cannot read from the file (match.csv); you can only read from the table you just LOADed - eg via SELECT date FROM bets , or SELECT STR_TO_DATE(date, 'YYYY-MM-DD') FROM bets - this will get you a result set which contains the date fields from all rows of the table.

what to do after / instead of that depensds on what you want to do with the data - what do you want to do with the bets.date´s in your SQL ?

so for order I should:

- load first the table match with all the info from the csv;
- then perform a select to convert the data from column date;

To do the last step what do you recommend?!
I was thinking of create an aux column to import the date as varchar and then convert it to the appropriate format.

any recommendations must depend on an answer to the questions:
a) what format is the date field in match.csv ?
b) what do you want to DO with the dates ? do you just want to normalize dates that are not in format YYYY-MM-DD so that they csn be put into a column of type DATE ?

if b) is true, try

UPDATE bets SET date=STR_TO_DATE(date,'YYYY-MM-DD')

or use another column of type DATE and replace date= with its name (plus =).