mysql ON DUPLICATE KEY UPDATE. Is it possible to refer to the values from the INSERT statement as an entity rather than one field at a time?

Question

I haven't found a syntax to do this, what I find frustrating is that I haven't seen anything that specifically says it can't be done, so I keep searching.

With a table "table_one", with one unique field "table_one.a" which is the primary key, is it possible to do something like this:

INSERT INTO `table_one` (a,b,c,d,e,f,g,h) VALUES (1,2,3,4,5,6,7,8)
  ON DUPLICATE KEY UPDATE VALUES();

I'm aware of the following approaches:

INSERT INTO `table_one` (a,b,c,d,e,f,g,h) VALUES (1,2,3,4,5,6,7,8)
  ON DUPLICATE KEY UPDATE `b` = '2', `c` = '3', `d` = '4', `e` = '5', `f` = '6', `g` = '7', `h` = '8';

And

INSERT INTO `table_one` (a,b,c,d,e,f,g,h) VALUES (1,2,3,4,5,6,7,8)
  ON DUPLICATE KEY UPDATE `b` = 'VALUES(b)', `c` = 'VALUES(c)', `d` = 'VALUES(d)', `e` = 'VALUES(e)', `f` = 'VALUES(f)', `g` = 'VALUES(g)', `h` = 'VALUES(h)';

I'm hoping that (if it's not possible) someone can point me to a reference that explains why.

score 2 · Accepted Answer · answered Mar 04 '12 at 02:50

2

It's not possible because it violates the UPDATE syntax, so you must list every column. You may look into using REPLACE INTO, but be careful as that actually deletes the row and adds it again, so you may lose some values -- it also triggers foreign key ON DELETEs. However, it is a much less verbose way to do what you want.

answered Mar 04 '12 at 02:50

Explosion Pills

188,624
52
326
405

That you can use VALUES with REPLACE INTO is the reason it bothers me that you can't with ON DUPLICATE KEY UPDATE. I'll accept "what is" and move on with a little less weight on my shoulders. Good job mentioning the pitfalls of using REPLACE INTO. – codewaggle Mar 04 '12 at 03:03

score 1 · Answer 2 · edited May 23 '17 at 10:24

1

No, it's not possible.

There is no reference that explains why: there is simply no syntax that allows you to do it.

edited May 23 '17 at 10:24

Community

1
1

answered Mar 04 '12 at 02:53

Lightness Races in Orbit

378,754
76
643
1,055

I spent hours searching for info about this question, feeling a bit awkward about missing such a similar request. – codewaggle Mar 04 '12 at 03:09

mysql ON DUPLICATE KEY UPDATE. Is it possible to refer to the values from the INSERT statement as an entity rather than one field at a time?

2 Answers2