Conditional on duplicate key update not working correctly

Question

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 ;

What exactly do you want to achieve? The data you provided doesn't answer this condition. — sagi, Jun 05 '16 at 07:58
Value of `created_on` will be set to `NOW()` as per the code you have written. The value of `is_active` is 0 when your statement executes. In your case statement, you are saying that `created_on` should be updated to `NOw()` when `is_active` is 0. — Incognito, Jun 05 '16 at 08:01

ysth · Accepted Answer · 2016-06-05T08:55:56.743

2

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.

edited Jun 05 '16 at 08:55

answered Jun 05 '16 at 08:25

ysth

96,171
6
121
214

I didn't know that @ysth. Thanks. But I think this needs to be done using triggers, the way ring bearer describes in his answer, since I need to know the previous value of `is_active`. I have upvoted you, and it'll display once I have 15 reps. – Shoaib Ahmed Jun 05 '16 at 08:37
ah, I didn't notice that part of your question. you *don't* need to use a trigger; I will update my answer – ysth Jun 05 '16 at 08:48

ring bearer · Answer 2 · 2016-06-05T14:24:08.057

0

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.

edited Jun 05 '16 at 14:24

answered Jun 05 '16 at 08:01

ring bearer

20,383
7
59
72

Thanks. I'm new to triggers. I just tried your solution, and I get a syntax error `ERROR 1064 (42000):` near `near '== '0' && NEW.is_active == '1' THEN`. I'm editing my question to add the exact query I'm using. Could you please have a look. – Shoaib Ahmed Jun 05 '16 at 08:22
And I'm sorry I can't upvote you owing to my meagre reputation. :( – Shoaib Ahmed Jun 05 '16 at 08:27

Conditional on duplicate key update not working correctly

EDIT 1:

EDIT 2:

2 Answers2

Linked

Related