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?
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.
Unfortunately not.
You can get half-way there by not having to repeat the value:
But you still have to list the columns.
i know this is an old question, and this is a somewhat unconventional answer, but i ran into the same thing and was having issues with setting the group_concat_max_len property mentioned in another answer and was always getting a truncated result. Another option that I ultimately went with when writing a long script was to use this formula in excel:
where A1 is the cell you copy the field name into. To do this quickly, I'd right click the table and copy the select statement to the clipboard, which gives you all the colum names, the formula removes the commas.
Hope this helps someone!
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:
and its output is this:
on a table that has columns a,b,c and d, so you can append to the first part of the query:
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):