MYSQL - insert if it does not exist but update only if it satisfy a criteria

user_1053496 0
Viewed 25

I would like to get a help for a mysql ( using version 8 ) case. I have following table

CREATE TABLE taskStatus (  task_id int NOT NULL,employee_id int NOT NULL, completed_on datetime DEFAULT NULL,task_status bit(1) NOT NULL,  PRIMARY KEY (task_id,employee_id)) ENGINE=InnoDB

I would like to insert a row if (task_id,employee_id) does not exist but if it exists then it should update the row only if task_status = 0. By this way I want to ensure that if somebody set task_status = 1 for a row then never change the completed_on value. INSERT INTO ….. ON DUPLICATE KEY UPDATE will not work as it won’t take a where clause. For me, UPDATE should happen only if one column value satisfied a criteria but INSERT can happen without that condition.

I understood that I could do it using multiple queries but I have to do it for lot of rows.
My understanding is that if I can do it in a single query then I can run it as a batch. If I do it in multiple query then I can not do it as batch and whole thing may be too slow.

Could someone please suggest some pointers.

mysql
1 Answers

Use the INSERT ... ON DUPLICATE KEY UPDATE statement like ethis:

INSERT INTO taskStatus(task_id, employee_id, completed_on, task_status) VALUES
(?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    completed_on = VALUES(completed_on),
    task_status = CASE WHEN task_status THEN task_status ELSE VALUES(task_status) END;

The CASE expression will prevent to update task_status if it is already 1.
If you also want completed_on not to be updated if task_status is 1, use a similar CASE expression for it too:

completed_on = CASE WHEN task_status THEN completed_on ELSE VALUES(completed_on) END

Replace the ? placeholders with the values of the columns that you want to insert.

user_104988280
Related