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?

---

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;

---

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

---

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