My query look actually to :
INSERT INTO `table` (f1,f2,status) VALUES
(1,5,'on'), (3,2,'on'), (15,20,'on')
ON DUPLICATE KEY UPDATE status='off';
But how to add IF status="on" then do this update ? I tried
INSERT INTO `table` (f1,f2,status) VALUES
(1,5,'on'), (3,2,'pending'), (15,20,'on')
ON DUPLICATE KEY UPDATE status='off' IF(status='pending');
But seems doesn't work, I didn't understand well this part in documentation.
Any help please?
Thanks
Here is a good documentation and example demonstrating the same MySQL IF Function.
Syntax of the MySQL IF function is as follows: IF(expr, if_true_expr, if_false_expr)
If the expr evaluates to TRUE, the IF function returns the if_true_expr, otherwise it returns if_false_expr.
Now, coming to your question, assuming you want to update the status with 'on' or 'off' or 'pending' or user input for column status based on status value as on , the query should be like below:
INSERT INTO `table` (f1,f2,status) VALUES
(1,5,'on'), (3,2,'pending'), (15,20,'on')
ON DUPLICATE KEY UPDATE status = IF(VALUES(status) = 'on', VALUES(status), 'off');