douzhang5295
douzhang5295
2015-06-27 11:34

使用INSERT ... ON DUPLICATE KEY UPDATE和几个键

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douhunkuang8955 douhunkuang8955 6年前

    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.

    点赞 评论 复制链接分享
  • dqy0707 dqy0707 6年前

    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
    
    点赞 评论 复制链接分享

相关推荐