Using INSERT … ON DUPLICATE KEY UPDATE with several keys

Question

I have a DB in which I would like one of three keys (userId, udid and token) to identify the row: all three of them are unique keys and the first is also primary and autoincremental. Basically I would like to update all the values when one of the keys is the same. Unfortunately if I use:

INSERT INTO users (udid, nickname, playerID, `language`, app, token,
 `In Arrivo HD`) VALUES ('AB71C145-2FFE-4BA8-B0E7-9F121948C962', 'Fabry65', 'G:274138044', 'it_IT', 'In Arrivo HD', 
'605a383a7e3469a3da0b471cd5b73af7384ca5e389eeb7cd72e550e96f37f450', NOW()+ INTERVAL 1 YEAR) 
ON DUPLICATE KEY UPDATE udid='AB71C145-2FFE-4BA8-B0E7-9F121948C962', 
token='605a383a7e3469a3da0b471cd5b73af7384ca5e389eeb7cd72e550e96f37f450',  lastAccess=NOW(), active=1, nickname='Fabry65',  
playerID='G:274138044',`language`='it_IT', app='In Arrivo HD'

And the key to be repeated happens to be the token, I have error:

Duplicate entry '605a383a7e3469a3da0b471cd5b73af7384ca5e389eeb7cd72e550e96f37f450' for key 'token'

And the same would of course happen if it were the other the two other keys to be repeated. Chaining ON DUPLICATE KEY UPDATE's does not seem to work. How may I do it?

In brief my issue is updating a row if any of three keys are the same, and inset a new row if all of them are different.

score 0 · Answer 1 · answered Jun 27 '15 at 11:54

Well I think you misunderstand the option ON DUPLICATE KEY. YOu don't determine what should happen if a duplicate key is found.

Here is an example out of the MySQL Docs.

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

This means, if a duplicate key is found, c should be increased by 1 (c=c+1).

In your case, you just have defined if a duplicate key is found, set it to a specific value. This isn't your intention, doesn't it?

Maybe you just want to generate a new unique identifier and update the column with that value? Something like this for example:

INSERT INTO users (udid, nickname, playerID, `language`, app, token,
 `In Arrivo HD`) VALUES ('AB71C145-2FFE-4BA8-B0E7-9F121948C962', 'Fabry65', 'G:274138044', 'it_IT', 'In Arrivo HD', 
'605a383a7e3469a3da0b471cd5b73af7384ca5e389eeb7cd72e550e96f37f450', NOW()+ INTERVAL 1 YEAR) 
ON DUPLICATE KEY UPDATE udid=uuid() -- which will generate a new Unique identifier for those column instead

As a matter of fact I ended up splitting the query. I left this one, taking away the token part, if userId was null. Otherwise I did a simple update on userId, considering this identifier is given by the system and the user is consequently already there if not null. — Fabrizio Bartolomucci, Jun 27 '15 at 14:15
yet it does not solve everything: when I do: UPDATE users SET token='605a383a7e3469a3da0b471cd5b73af7384ca5e389eeb7cd72e550e96f37f450', udid='AB71C145-2FFE-4BA8-B0E7-9F121948C962', lastAccess=NOW(), active=1, nickname='Fabry65', playerID='G:274138044',`language`='it_IT', app='In Arrivo HD' WHERE userId=10025 with the token already assigned, I get the duplicate error all the same. — Fabrizio Bartolomucci, Jun 27 '15 at 14:17

score 0 · Accepted Answer · answered Jun 27 '15 at 14:31

Ok, I think I found a solution:

$query = "UPDATE users SET token='token', udid='$udid', lastAccess=NOW(), active=1, nickname='".$nickname."', playerID='".$playerID."',`language`='".$language."', app='".$app."' WHERE userId=$userId";
    $resultUpdate=$mysqli->query($query);
    if (!$resultUpdate){
        $query = "UPDATE users SET udid='$udid', lastAccess=NOW(), active=1, nickname='".$nickname."', playerID='".$playerID."',`language`='".$language."', app='".$app."' WHERE userId=$userId"
    }

I execute the first query trying to also update the token, if it fails, it means the token is the same and so I update without mentioning the token.

Using INSERT … ON DUPLICATE KEY UPDATE with several keys

2 Answers2