CODEWITHSUNDEEP
phpmysql
Patrick
Patrick

Reputation: 4905

multiple insert on duplicate

I'm trying to do this:

INSERT INTO table (id, hits) VALUES ('abc', 1), ('cde', 1), ('fgh', 1)
ON DUPLICATE KEY UPDATE hits = VALUES (2), (6), (10)

The code is wrong (probably would need to put the id in the key update) but is there anyway to do this?

Upvotes: 0

Views: 94

Answers (3)

cypher
cypher

Reputation: 6992

You need to use VALUES properly, it's really rather simple:

INSERT INTO table (id, hits) VALUES ('abc', 1), ('cde', 1), ('fgh', 1)
ON DUPLICATE KEY UPDATE hits = VALUES (hits)

EDIT: if you desperately need to assign hits different values in single query on duplicate key than in the insert query, try CASE operator.

INSERT INTO table (id, hits) VALUES ('abc', 1), ('cde', 1), ('fgh', 1)
ON DUPLICATE KEY UPDATE hits = CASE VALUES(id) 
    WHEN 'abc' THEN 3
    WHEN 'cde' THEN 2
    WHEN 'fgh' THEN 28
    ELSE 11 -- this is a default value assuming you need one
    END;

Upvotes: 1

Johan
Johan

Reputation: 76577

If you want to just set hits to 2 it's easy, do:

INSERT INTO table (id, hits) VALUES ('abc', 1), ('cde', 1), ('fgh', 1)
ON DUPLICATE KEY UPDATE hits = 2;

If you want to update the values with different hits per id, you need to use a different way of doing things:

From the MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example: 

INSERT INTO table (id, hits) VALUES ('abc', 1), ('cde', 1), ('fgh', 1)
ON DUPLICATE KEY UPDATE hits = VALUES(hits)+1

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

INSERT INTO table (id, hits) 
  VALUES ('abc', 1), ('cde', 2), ('fgh', 3)
ON DUPLICATE KEY 
  UPDATE hits = hits + VALUES (hits) ;    --- increase by 1, 2, 3 accordingly

or

  UPDATE hits = VALUES (hits) ;           --- set to 1, 2, 3 accordingly

Upvotes: 0

Related Questions