Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?

Naftali aka Neal picture Naftali aka Neal · Mar 2, 2012 · Viewed 60.4k times · Source

I know that you can use ON DUPLICATE KEY UPDATE to update a certain value if there is a record for that key already,

I can do this:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE `a`=1, `b`=2, `c`=3

But how can I do this without having to write out the columns and values twice?

Answer

Lightness Races in Orbit picture Lightness Races in Orbit · Mar 2, 2012

Unfortunately not.

You can get half-way there by not having to repeat the value:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

But you still have to list the columns.