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: Peter Brawley
Date: March 22, 2021 01:59PM

Please post relevant code on this page, inside BBCode code tags for readability.

Your snippet runs without warning in 5.7 ...

create table names (
  id int auto_increment primary key,
  name varchar(255)
);
insert into names (name) values ('foo');

create table copy like names;
insert into copy (name) values ('bar');

insert into names select * from copy
on duplicate key update name = values(name);

show warnings;

select * from names;

SELECT VERSION();

... but elicits this warning in 8.0.23 ...

"Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead."

Quote

1. explain how to adapt INSERT INTO SELECT cases like this

Following the INSERT...SELECT...ON DUPLICATE... explanation & examples at https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html, we find this works ...

select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
INSERT INTO names(id,name)
 SELECT id,name from copy
 ON DUPLICATE KEY UPDATE name = copy.name;
select * from names;
Query OK, 0 rows affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
select * from names
+----+------+
| id | name |
+----+------+
|  1 | bar  |
+----+------+

Quote

2. update the documentation and/or release notes to cover that case

Most responders here including me are volunteers, not Oracle employees. After reading that section, I didn't have difficulty making a working example that avoids the deprecated syntax, so if the cited manual page needs clarification, what would that be?

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.