doushichi3678
doushichi3678
2013-01-15 15:37

ON DUPLICATE KEY插入新行而不是更新

已采纳

I have a little problem.

This is the SQL code I get:

INSERT INTO matches (match_id, league_name, league_id, test_one) 
VALUES (866860, 'Portugal',1,'testing') 
ON DUPLICATE KEY 
UPDATE league_name = 
  CASE match_id 
    WHEN 866860 
      THEN 'Portugal' END, 
league_id = 
  CASE match_id 
    WHEN 866860 
      THEN 1 END, 
test_one = 
  CASE match_id 
    WHEN 866860 
      THEN 'testing' END

The problem is, that it always insert a new row instead of updating the existing one. Is it because I need to make the "match_id" as AUTO_INCREMENT or anything else (at the moment, my "id" field is AUTO_INCREMENT)

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

2条回答

  • dousunle5801 dousunle5801 8年前

    AUTO_INCREMENT is irrelevant here. But ON DUPLICATE KEY requires a key. More specifically, a unique key, such as primary key or a unique index.

    Given the column names, I suspect that match_id fails to be a primary key.

    Update: You also write a complicate set of CASE ... END constructs. Have a look at the VALUES() function.

    点赞 评论 复制链接分享
  • doukanhua0752 doukanhua0752 8年前

    You are telling your database to update information "ON DUPLICATE KEY"; if none of the other fields are UNIQUE keys, the database will insert a new row.

    You can either SELECT the id before this query (or making it a sub-query) or add another key to this table that will result in a unique value for each row. I don't know about your schema, but you might be able to create a multi-part key which spans match_id and league_id.

    点赞 评论 复制链接分享

相关推荐