CODEWITHSUNDEEP
mysqlsqlpdo
wow
wow

Reputation: 3899

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:

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

Upvotes: 24

Views: 30806

Answers (3)

Szila73
Szila73

Reputation: 11

I made a shocking discovery regarding the INSERT INTO ... ON DUPLICATE KEY UPDATE statement. If the values of some fields in the UPDATE clause depend on the original value of another field, the order of the fields is crucial.

INSERT INTO tablename (primari_key_column, column_datetime, column3, column4)
     VALUES ('primari_key_value', 'datetime_value', 'value3', 'value4') 
ON DUPLICATE KEY UPDATE column3 = IF(column_datetime <= 'datetime_value', 'value3', column3)
                      , column4 = IF(column_datetime <= 'datetime_value', 'value4', column4)
                      , column_datetime = IF(column_datetime <= 'datetime_value', 'datetime_value', column_datetime);

The example shows the correct usage, i.e. the field to be modified last is the field whose value determines whether the new values are to be placed in the affected row or whether the old values are to remain in it.

INSERT INTO tablename (primari_key_column, column_datetime, column3, column4)
     VALUES ('primari_key_value', 'datetime_value', 'value3', 'value4') 
ON DUPLICATE KEY UPDATE column_datetime = IF(column_datetime <= 'datetime_value', 'datetime_value', column_datetime)
                      , column3 = IF(column_datetime <= 'datetime_value', 'value3', column3)
                      , column4 = IF(columnn_datetime <= 'datetime_value', 'value4', column4);

In this INCORRECT case, only the modification of the column_datetime field takes place, the others don't because the condition is no longer met afterwards.

This behaviour is definitely the case for AWS RDS Aurora 5.7 (MySQL 5.7.12).

Upvotes: 1

Arustamyan G.
Arustamyan G.

Reputation: 721

you're using IF statement and VALUES() function incorrectly

INSERT INTO userlist (username, lastupdate, programruncount, ip)
VALUES (:username, NOW(), 1, :ip)
ON DUPLICATE KEY UPDATE
lastupdate = IF(NOW() > lastupdate + INTERVAL 20 MINUTE, NOW(), lastupdate),
programruncount = IF(NOW() > lastupdate + INTERVAL 20 MINUTE, programruncount + 1,   programruncount),
ip = IF(NOW() > lastupdate + INTERVAL 20 MINUTE, VALUES(ip), ip);

so IF checks for a condition and return one of two values provided as its parameters. See MySQL's Flow Control Operators.

Upvotes: 65

acecoolz
acecoolz

Reputation: 1

i always do it this way:

INSERT INTO userlist (username, lastupdate, programruncount, ip) VALUES (:username, NOW(), 1, :ip)
ON DUPLICATE KEY UPDATE
lastupdate = IF(lastupdate > NOW() - INTERVAL 20 MINUTE, NOW(), lastupdate),
programruncount = IF(lastupdate > NOW() - INTERVAL 20 MINUTE, programruncount + 1, programruncount),
ip = IF(lastupdate > NOW() - INTERVAL 20 MINUTE, VALUE(ip), ip);


VALUE(ip) --> update with new value
ip --> will not update the field

Upvotes: 0

Related Questions