IF ELSE inside Load data infile mysql

Question

I am trying to insert some data using Load data infile into a mysql table that already has some data. The table contains id and name. My csv file contains three fields: id, name and code. The table schema also has these three fields, but currently, the table has NULL for the code field. I am trying to insert the code from csv to an existing row in the table if it matches the name, else I am trying to insert a complete new row.

The code I have tried is as follows:

   LOAD DATA INFILE 'table1.csv'
   INTO TABLE table1
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n'
   IGNORE 1 LINES
   (@code, @name, @other_columns)
   IF EXISTS (SELECT * FROM table1 where name=@name);
   BEGIN
   set code=@Code;
   END
   ELSE
   BEGIN
   set code=@Code, name=@name;
   END

By doing so, I am getting a mysql syntax error, but am unable to figure it out. Can anyone point me in the right direction, or suggest to me another approach? I have thousands of new rows to insert and thousands of existing rows to modify based on the certain field, (name in this case).

Andrew Andrew · Accepted Answer · 2017-11-16T20:54:11

MySQL does not allow the LOAD DATA INFILE statement inside a stored program, which is where the IF statement appears. Break up your task into two parts. First, LOAD DATA INFILE into a temporary table. Then create a stored program that replaces the loaded data into your table1 from the temporary table.

IF ELSE inside Load data infile mysql

1 Answers