INSERT INTO values (...) ON DUPLICATE KEY UPDATE with multiple values

Question

Will this work? (i dont have a mysql server available to test >.< )

mytable:
myid : integer/primary key
mydata : text

INSERT INTO mytable VALUES (1,"new row"),(2,"brand new row"),(3,"yup another new row"), [.....more and more coma-separated parentheses with values], (1000,"guess what? yes new row") ON DUPLICATE KEY UPDATE mydata = "dang, this row already exists!";

Answer 1

Yes, it will work, but it is advisable to explicitly name the columns first:

INSERT INTO mytable
  (myid, mydata)
   VALUES (1,"new row"),(2,"brand new row"),(3,"yup another new row")

Also, single quotes are a little more syntactically portable, though MySQL will handle them correctly.

   VALUES (1,'new row'),(2,'brand new row'),(3,'yup another new row')

Note, if you already have values in mytable, you may encounter primary key collisions for id. You will need to decide what action should be taken in that circumstance, and apply ON DUPLICATE KEY accordingly.

Answer 2

I don't see why not:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

from http://dev.mysql.com/doc/refman/5.5/en/insert.html. There's even an example here

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);