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 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
After that I get status fields NULL.
How to resolve this problem?
Edit:
I tried:
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),
But result status 2 changed to 0.
Table schema:
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;
Real data:
| 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 |
File data:
| 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 |
My temporary solution:
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));