3

So I have the following table:

mysql> show create table user_api_skills \G
*************************** 1. row ***************************
       Table: user_api_skills
Create Table: CREATE TABLE `user_api_skills` (
  `characterID` int(11) NOT NULL,
  `typeID` int(11) NOT NULL,
  `level` enum('0','1','2','3','4','5') NOT NULL DEFAULT '0',
  `skillpoints` int(11) NOT NULL,
  `currentTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`characterID`,`typeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

And in that table a row which I am trying to insert/update:

mysql> SELECT * FROM `user_api_skills` WHERE `characterID` =93192782 AND `typeID` =3359;
+-------------+--------+-------+-------------+---------------------+
| characterID | typeID | level | skillpoints | currentTime         |
+-------------+--------+-------+-------------+---------------------+
|    93192782 |   3359 | 3     |      135765 | 2013-09-30 16:58:35 |
+-------------+--------+-------+-------------+---------------------+
1 row in set (0.00 sec)

I believe my query is correctly formed and when executed it doesn't throw any errors or warnings:

mysql> INSERT INTO user_api_skills (characterID,typeID,level,skillpoints)
VALUES (93192782,3359,4,135765) ON DUPLICATE KEY UPDATE level=4,             
skillpoints=135765,currentTime=NOW();
Query OK, 2 rows affected (0.22 sec)

I get 2 rows updated (as I would expect from an insert on dup update)

mysql> SELECT * FROM `user_api_skills` WHERE `characterID` =93192782 AND `typeID` =3359;
+-------------+--------+-------+-------------+---------------------+
| characterID | typeID | level | skillpoints | currentTime         |
+-------------+--------+-------+-------------+---------------------+
|    93192782 |   3359 | 3     |      135765 | 2013-09-30 16:59:13 |
+-------------+--------+-------+-------------+---------------------+
1 row in set (0.00 sec)

mysql> 

but the row itself only changes a single value (the currentTime). Can anybody explain why the other two fields are not updating?

ModulusJoe
  • 1,416
  • 10
  • 17
  • skillpoints is the same value ? – neildt Sep 30 '13 at 16:14
  • 1
    Sorry, I have solved this myself. The level field is an ENUM and the query specified the new value as a number. Updating the query to the following resulted in the expected results. `mysql> INSERT INTO user_api_skills (characterID,typeID,level,skillpoints) VALUES (93192782,3359,4,135765) ON DUPLICATE KEY UPDATE level='4', skillpoints=135765,currentTime=NOW();` – ModulusJoe Sep 30 '13 at 16:17
  • Post that as an answer and accept your own answer. – Mihai Sep 30 '13 at 16:25
  • I don't have enough rep to answer my own question, I have to wait 7 more hours. – ModulusJoe Sep 30 '13 at 16:28

1 Answers1

0

Sorry, I have solved this myself. The level field is an ENUM and the query specified the new value as a number. Updating the query to the following resulted in the expected results.

mysql> INSERT INTO user_api_skills (characterID,typeID,level,skillpoints) VALUES 
(93192782,3359,4,135765) ON DUPLICATE KEY UPDATE level='4', skillpoints=135765, 
currentTime=NOW();

By providing a int to the update it updated to the one based number choice of the enum, so in this instance, the 4th choice is '3'.

ModulusJoe
  • 1,416
  • 10
  • 17