CODEWITHSUNDEEP
mysqlconditional-statementson-duplicate-key
Shoaib Ahmed
Shoaib Ahmed

Reputation: 424

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 ;

Upvotes: 0

Views: 722

Answers (2)

ring bearer
ring bearer

Reputation: 20783

I would suggest using an update trigger to get this working. In that trigger you would track change to is_active field and set required field to required value. Example:

DELIMITER //

CREATE TRIGGER created_on_after_update
BEFORE INSERT
   ON my_table FOR EACH ROW

BEGIN
       IF (NEW.is_active = '1') THEN
         //Set required change.
       END IF;
END; //

DELIMITER ;

During insert you can just check the NEW.is_active and take decision as shown above.

Upvotes: 0

ysth
ysth

Reputation: 98398

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.

Upvotes: 2

Related Questions