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

Question

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?

http://stackoverflow.com/questions/294661/what-is-the-best-way-to-insert-into-and-update-a-single-row-table-in-mysql — Lightness Races in Orbit, Mar 02 '12 at 17:36

score 129 · Accepted Answer · answered Mar 02 '12 at 17:36

129

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.

answered Mar 02 '12 at 17:36

Lightness Races in Orbit

378,754
76
643
1,055

What if I need to add some value if the key already exists. And insert a new value if it doesnt. For example: INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3) ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b` + b.old), `c`=VALUES(`c` + c.old ); or something similar. – Sohaib Sep 02 '14 at 04:49
3

@Sohaib: `UPDATE a = VALUES(a)+a, b = VALUES(b)+b, c = VALUES(c)+c`? – Lightness Races in Orbit Sep 02 '14 at 08:36
7

The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_values – Vishal Maral Mar 18 '20 at 09:51

score 51 · Answer 2 · edited Oct 10 '17 at 18:13

51

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.

edited Oct 10 '17 at 18:13

Wes

826
9
18

answered Mar 02 '12 at 17:40

5

Ah, that might be much better. Worth noting that all fields not given explicitly in the query will be defaulted, though, which obviously differs from the `ON DUPLICATE KEY UPDATE` behaviour. – Lightness Races in Orbit Mar 02 '12 at 18:30
76

Note: with `REPLACE`, if the record's key matches up with something that already exists, **the old row(s!) will be deleted, and the new one inserted**. This can be a big deal if you mess with triggers or foreign key constraints. – cHao Mar 02 '12 at 18:40
3

Good point. You're risking some substantial chain reactions, I guess. – Lightness Races in Orbit Mar 02 '12 at 18:41
1

Also note mysqli_insert_id() does not work! "If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero." – Edward Nov 01 '13 at 01:10
1

To elaborate on cHao's comment, this means that your new entries will have **different auto increment ids** than the old "replaced" entries. This can be pretty awful if you are using a linking table somewhere that doesn't catch the replace – Abraham Brookes Feb 27 '19 at 00:48
...and in addition, it doesn't work with constraints to other tables. MariaDB result is: `ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails` – sneaky Jul 10 '23 at 06:38

Javier P · Answer 3 · 2017-06-08T15:37:02.373

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;

Love this. Sometimes I need this before your very helpful query. SET SESSION group_concat_max_len = 1000000; — Uncle Iroh, Nov 08 '16 at 20:53

mowwwalker · Answer 4 · 2020-04-01T15:10:41.923

per @BhendiGawaar's comment on @Lightness Races in Orbit's answer, here is the MySQL 8.019+ version:

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

OR

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

Works with set as well:

INSERT INTO t1 SET a=1,b=2,c=3 AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

Copied directly from MySQL Docs

The deprecation warning about the use of VALUES:

The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section.

score 0 · Answer 5 · answered Apr 06 '18 at 10:59

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:

=SUBSTITUTE(TRIM(A1), ",", "") & " = VALUES(" & SUBSTITUTE(TRIM(A1), ",", "") & "),"

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!

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

5 Answers5

Linked