Load local infile data with IF statement
I have a table with data:
| id | status |
+-----+--------+
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |
| 4 | 2 |
| 5 | 2 |
I have a 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 the status in the table = 2 and the status in the file = 0, leave the status in the table = 2, otherwise replace the status in the table from the file.
Upon request, I need to get new data:
| id | status |
+-----+--------+
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
| 4 | 2 |
| 5 | 1 |
I am trying to do this with a request:
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 NULL status fields.
How to solve this problem?
Edit:
I've 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 the state of the result 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 workaround:
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));
source to share
I think this should be enough:
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(status = 2 and @stts = 0, status, @stts),
teacher_id = @tid, prod_id = @pid;
If that doesn't work, you can try using the function values()
, although it says it's for the operator INSERT ... ON DUPLICATE KEY UPDATE
.
In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES (col_name) function in the UPDATE clause to refer to the column values from the INSERT part of the statement. In other words, the VALUES (col_name) in the UPDATE clause refers to the col_name value, which will be inserted if there is no duplicate key conflict. This feature is especially useful in multi-line inserts. The VALUES () function only makes sense in the ON DUPLICATE KEY UPDATE clause of INSERT statements, and returns NULL otherwise. See Section 13.2.5.3, "INSERT ... INTO KEY CONNECTION".
If that doesn't help, please specify the table schema, etc., so we can try ourselves and don't need to guess.
source to share