MySQL Forums
Forum List  »  Docs

Re: Suggested replacement for use of deprecated VALUES() to access new row values in INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statements
Posted by: Jens Hatlak
Date: March 26, 2021 06:30AM

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))
  -- ...
;

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.