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

[Peter Brawley]

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?