Re: Suggested replacement for use of deprecated VALUES() to access new row values in INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statements

[Jens Hatlak]

I only provided a very simple example which can indeed easily be rewritten.

Consider a more complex one:

insert into foo select
  some(function(calls(b1, b2, b3))),           # target: f1
  some(more(function(calls(b4), b5))),         # target: f2
  some(other(function(calls(b6, b7)), b8))     # target: f3
  -- ...
from bar on duplicate key update
  f1 = values(f1),
  f2 = values(f2),
  f3 = values(f3)
  -- ...
;

Here the current syntax very conveniently allows to specify all the call/calculation logic only once, irrespective of whether an insert or update will be performed in the end.

With the VALUES deprecation/removal, that will have to be rewritten, resulting in heavily redundant specifications.

AFAICT no-one really cares about that loss of convenience, and it is not explicitly documented that this is not possible anymore either.

So if there really is no viable alternative to the current syntax for that case, then at least the documentation (or release notes) should state that in an obvious manner, with an example. I already provided the first part above; the second part would be something like this:

insert into foo select
  some(function(calls(b1, b2, b3))),
  some(more(function(calls(b4), b5))),
  some(other(function(calls(b6, b7)), b8))
  -- ...
from bar on duplicate key update
  f1 = some(function(calls(b1, b2, b3))),
  f2 = some(more(function(calls(b4), b5))),
  f3 = some(other(function(calls(b6, b7)), b8))
  -- ...
;