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

Question

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)

Answer 1

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.