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

mysql insert on-duplicate-key
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.

Related questions

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either: ON DUPLICATE KEY UPDATE which implies an unnecessary update …

Read More
How to set initial value and auto increment in MySQL?

How do I set the initial value for an "id" column in a MySQL table that start from 1001? I want to do an insert "INSERT INTO users (name, email) VALUES ('{$name}', '{$email}')"; Without specifying the initial …

Read More
How to copy a row and insert in same table with a autoincrement field in MySQL?

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2. How can I do this in a single query?

Read More