mardi 30 décembre 2014

Load data local infile with IF statement

I have table with data:



| id | status |
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |
| 4 | 2 |
| 5 | 2 |


I have file, that I need to load into this table and replace:



| id | status |
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 1 |


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.

After query I need to get new data:



| id | status |
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
| 4 | 2 |
| 5 | 1 |


I'm trying do it with query:



load data local
infile '".$file."'
replace
into table tec_tca_teacher_prod_copy
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 tec_tca_teacher_prod_copy where teacher_id=@tid and prod_id=@pid) as tmp)=2,status,@var),
teacher_id = @tid, prod_id = @pid


After that I get status fields NULL.


How to resolve this problem?


Aucun commentaire:

Enregistrer un commentaire