Sufi Al Hussaini Sufi Al Hussaini - 5 months ago 31
MySQL Question

Conditional on duplicate key update not working correctly

I need to update my

created_on
datetime field value to
NOW()
only if the old value for
is_active
field was 0 and it changed to 1.

+-----------+---------+-----------+---------------------+---------------------+
| device_id | user_id | is_active | created_on | last_modified_on |
+-----------+---------+-----------+---------------------+---------------------+
| 5 | 5 | 0 | 2016-06-05 03:31:48 | 2016-06-05 03:31:48 |


Here's what I've got so far:

INSERT INTO `device2users`
(`device_id`, `user_id`, `is_active`, `created_on`, `last_modified_on`)
VALUES
(5, 5, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
`created_on` = CASE WHEN `is_active` <> 0 THEN VALUES(`created_on`) ELSE NOW() END,
`is_active`=1, `last_modified_on`=NOW();


But it's not working, and the
created_on
field's value is always set to
NOW()
.

EDIT 1:



I want to update the value for
created_on
field to NOW()
ON DUPLICATE KEY
, only if the
is_active
field's value was
0
before and is
1
in the specified query.

EDIT 2:



I'm using the following query based on @ring bearer's answer. But I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== '0' && NEW.is_active == '1' THEN


Here's the exact query I'm using.

DELIMITER //

CREATE TRIGGER created_on_after_update
AFTER UPDATE
ON `acd_device2users` FOR EACH ROW

BEGIN
IF OLD.is_active == '0' && NEW.is_active == '1' THEN
SET `created_on`=NOW();
END IF;
END; //

DELIMITER ;

Answer

You want just created_on where you have values(created_on). values returns the value the column would have been set to if there hadn't been a duplicate key (which is now() in your case), not the old value.

column names in the update part will return their old value if you use them before setting them. So to only change created_on if is_active is changing from 0 to 1, do:

ON DUPLICATE KEY UPDATE
    created_on = CASE WHEN is_active=0 && VALUES(is_active)=1 THEN VALUES(created_on) ELSE created_on END,
    is_active = VALUES(is_active),
    last_modified_on = VALUES(last_modified_on);

Using VALUES instead of hardcoded 1 or NOW() makes it properly use whatever values would have been used had it created a new row.

Comments