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

Solution 1:

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.

Solution 2:

use REPLACE INTO

The meaning of REPLACE INTO is that IF the new record presents new key values, then it will be inserted as anew record.

IF the new record has key values that match a pre-existing record,then the key violation will be ignored and the new record will replace the pre-existing record.

Solution 3:

If it is useful, I made a query to avoid writing by hand the last part of the "on duplicate" query, for versions >= 5.0:

SELECT GROUP_CONCAT( CONCAT(COLUMN_NAME,"=values(", COLUMN_NAME,")") SEPARATOR ", ") FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

and its output is this:

a=values(a), b=values(b), c=values(c), d=values(d)

on a table that has columns a,b,c and d, so you can append to the first part of the query:

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

UPDATE: For a very long list of columns you may see a truncated output, you may use this statement before the query above (thanks Uncle iroh):

SET SESSION group_concat_max_len = 1000000;