CODEWITHSUNDEEP
phpmysql
Simon Thomsen
Simon Thomsen

Reputation: 1421

ON DUPLICATE KEY insert new row instead of updating

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)

Upvotes: 0

Views: 1409

Answers (2)

Mechcozmo
Mechcozmo

Reputation: 303

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.

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146500

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.

Upvotes: 4

Related Questions