douzhang5295 2015-06-27 11:34
浏览 232
已采纳

使用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 2015-06-27 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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 怎么解决这种情况,求解
  • ¥15 cadence PEX
  • ¥15 phython创建了文件 显示在项目下面,但运行不了,运行时还是运行main文件,并且说main文件不允许并行运行
  • ¥20 C++哈希表的设计构造哈希表
  • ¥15 FutureWarning:不推荐使用空或全 NA 条目的 DataFrame 串联行为。怎么改呢?
  • ¥15 Chatgpt突然无法正常显示数学公式,如何解决?
  • ¥15 一个用华为模拟器做的实验。
  • ¥28 opencv Cuda C++编译
  • ¥15 插入sim卡4g模组反复断连
  • ¥100 如何把 16bit Bayer 图像数据转换为 TIFF RGB