title: Load data local infile with IF statement
body: <p>I have table with data:  </p>
  
  <pre><code>| id  | status |
  +-----+--------+
  |  1  |    1   |
  |  2  |    1   |
  |  3  |    0   |
  |  4  |    2   |
  |  5  |    2   |
  </code></pre>
  
  <p>I have file, that I need to load into this table and replace:  </p>
  
  <pre><code>| id | status |  
  +----+--------+
  |  1 |    1   |  
  |  2 |    0   |  
  |  3 |    0   |  
  |  4 |    0   |  
  |  5 |    1   | 
  </code></pre>
  
  <p>I have one condition: if status in table =2 and status in file =0, leave status in table =2, otherwise replace status in table from file.<br>
  After query I need to get new data:  </p>
  
  <pre><code>| id  | status |  
  +-----+--------+
  |  1  |    1   |  
  |  2  |    0   |  
  |  3  |    0   |  
  |  4  |    2   |  
  |  5  |    1   |  
  </code></pre>
  
  <p>I'm trying do it with query:  </p>
  
  <pre><code>load data local
  infile '".$file."'
  replace
  into table t1
  fields terminated by ',' enclosed by '\"'
  (@tid, 
  teacher_name, 
  email,
  @pid,   
  tca_form_type, 
  prod_company, 
  prod_name,
  @stts)
  set status = if((select status from (select status from t1 where teacher_id=@tid and prod_id=@pid) as tmp)=2  and @stts=0,status,@stts),
  teacher_id = @tid, prod_id = @pid  
  </code></pre>
  
  <p>After that I get status fields NULL.<br>
  How to resolve this problem?  </p>
  
  <p><strong>Edit:</strong><br>
  I tried:  </p>
  
  <pre><code>set status = if((select @var:=status from (select status from t1 where teacher_id=@tid and prod_id=@pid) as tmp)=2 and @stts=0,@var,@stts),  
  </code></pre>
  
  <p>But result status 2 changed to 0.  </p>
  
  <p><strong>Table schema:</strong></p>
  
  <pre><code>CREATE TABLE `table` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `teacher_id` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',
  `status` INT(11) NULL DEFAULT NULL,
  `prod_id` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `teacher_id_UNIQUE` (`teacher_id`, `prod_id`)
  )
  COLLATE='utf8_unicode_ci'
  ENGINE=InnoDB
  ROW_FORMAT=COMPACT
  AUTO_INCREMENT=2053;
  </code></pre>
  
  <p><strong>Real data:</strong>  </p>
  
  <pre><code>| id  | teacher_id | status | prod_id |
  +-----+------------+--------+---------+
  |  1  |    a1      |    1   |    15   |
  |  2  |    a1      |    1   |    16   |
  |  3  |    a1      |    0   |    17   |
  |  4  |    a2      |    2   |    16   |
  |  5  |    a2      |    2   |    18   |
  |  6  |    a3      |    0   |    15   |
  |  7  |    a3      |    1   |    20   |
  </code></pre>
  
  <p><strong>File data:</strong>  </p>
  
  <pre><code>| teacher_id | status | prod_id |
  +------------+--------+---------+
  |    a1      |    0   |    15   |
  |    a1      |    1   |    16   |
  |    a1      |    0   |    17   |
  |    a2      |    1   |    16   |
  |    a2      |    0   |    18   |
  |    a3      |    1   |    15   |
  |    a3      |    1   |    20   |  
  </code></pre>
  
  <p><strong>My temporary solution:</strong></p>
  
  <pre><code>load data local
                  infile '".$file."'
                  into table table_tmp
                  fields terminated by ',' enclosed by '\"'
                  (teacher_id, 
                  teacher_name, 
                  email,
                  prod_id,   
                  tca_form_type, 
                  prod_company, 
                  prod_name,
                  status);
  INSERT INTO table
                      (teacher_id, teacher_name, email, status, prod_id, tca_form_type, prod_company, prod_name)
                      SELECT teacher_id, teacher_name, email, `status`, prod_id, tca_form_type, prod_company, prod_name FROM table_tmp
                  ON DUPLICATE KEY UPDATE table.status = IF(table.status = 2 and VALUES(status) = 0, table.status, VALUES(status));
  </code></pre>