CODEWITHSUNDEEP
mysqlsql
Rogue
Rogue

Reputation: 11483

MySQL Query sets values to 0 with 'AND' clause

I have a MySQL query that is meant to update two columns by increments of 1, or create them if they do not exist.

My current table has four columns: id, username, playtime, and deathtime.

As it stands now, this is the query I use:

INSERT INTO `playTime` (`username`, `playtime`, `deathtime`)
VALUES ('Rogue', 1, 1)
ON DUPLICATE KEY UPDATE `playtime`=`playtime`+1
    AND `deathtime`=`deathtime`+1

(The username is dynamic in application, but one is provided for the example).

My issue, however, is that on a duplicate key it doesn't increment the values, rather it sets both values to 0. Through numerous tests of tweaking the query, I have found that it is the AND clause that causes the query to "reset" the values. For instance, this query will increment the playtime column correctly:

INSERT INTO `playTime` (`username`, `playtime`, `deathtime`)
VALUES ('Rogue', 1, 1)
ON DUPLICATE KEY UPDATE `playtime`=`playtime`+1

And if you swap "playtime" with "deathtime", then it increments the other column correctly. Why is this?

Upvotes: 1

Views: 97

Answers (2)

000
000

Reputation: 27247

Use a comma to delimit statements here.

INSERT INTO `playTime` (`username`, `playtime`, `deathtime`)
VALUES ('Rogue', 1, 1)
ON DUPLICATE KEY UPDATE `playtime`=`playtime`+1, `deathtime`=`deathtime`+1

The example can be seen in the documentation here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Upvotes: 4

Manu
Manu

Reputation: 901

Try this.. a comma, instead of AND

INSERT INTO `playTime` (`username`, `playtime`, `deathtime`)
VALUES ('Rogue', 1, 1)
ON DUPLICATE KEY UPDATE `playtime`=`playtime`+1 ,
     `deathtime`=`deathtime`+1

Upvotes: 4

Related Questions