John Davidson

Message:

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 lastupdate was less than 20 minutes ago (lastupdate > NOW() - INTERVAL 20 MINUTE).


• True: Update lastupdate = NOW(), add one to programruncount and then update ip = :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

---

My Answer:
The correct way to achieve this conditional update in MySQL is to use the CASE statement within the ON DUPLICATE KEY UPDATE clause. Here is the updated query:

sql
INSERT INTO userlist (username, lastupdate, programruncount, ip)
VALUES (:username, NOW(), 1, :ip)
ON DUPLICATE KEY UPDATE
lastupdate = CASE WHEN lastupdate > NOW() - INTERVAL 20 MINUTE THEN NOW() ELSE lastupdate END,
programruncount = CASE WHEN lastupdate > NOW() - INTERVAL 20 MINUTE THEN programruncount + 1 ELSE programruncount END,
ip = CASE WHEN lastupdate > NOW() - INTERVAL 20 MINUTE THEN :ip ELSE ip END;


This query will update the lastupdate, programruncount, and ip fields only if the condition (lastupdate > NOW() - INTERVAL 20 MINUTE) is true. Otherwise, it will leave all fields the same.