'Conditional ON DUPLICATE KEY UPDATE (Update only if certain condition is true)
I have the following query being used:
INSERT INTO userlist (username, lastupdate, programruncount, ip)
VALUES (:username, NOW(), 1, :ip)
ON DUPLICATE KEY UPDATE
lastupdate = NOW(), programruncount = programruncount + 1, ip = :ip;
However, I also want to make the ON DUPLICATE KEY UPDATE conditional, so it will do the following:
- IF
lastupdatewas less than 20 minutes ago (lastupdate > NOW() - INTERVAL 20 MINUTE). - True: Update
lastupdate = NOW(), add one toprogramruncountand then updateip = :ip. - False: All fields should be left the same.
I am not really sure how I would do this but after looking around, I tried using an IF Statement in the ON DUPLICATE KEY UPDATE part.
INSERT INTO userlist (username, lastupdate, programruncount, ip)
VALUES ("testuser", NOW(), "1", "127.0.0.1")
ON DUPLICATE KEY UPDATE
IF(lastupdate > NOW() - INTERVAL 20 MINUTE, VALUES(lastupdate, programruncount + 1),
lastupdate, programruncount);
However I am getting the following error: #1064 - 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 'IF(lastupdate > NOW() - INTERVAL 20 MINUTE, VALUES(lastupdate, programruncount +' at line 6
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
