MySQL 8 废弃 VALUES 函数在 INSERT ... ON DUPLICATE KEY UPDATE 中的使用如何解决?

新手上路,请多包涵

MySQL 8 INSERT ... ON DUPLICATE KEY UPDATE ... 'VALUES function' is deprecated and will be removed in a future release.

insert into pre_daily_role_active (`role_id`, `log_date`, `level`, `occupation`)
select `role_id`, '2024-05-28' as log_date, level, occupation
from (
     select role_id, logtime, level, `occupation`, row_number() over (partition by role_id order by logtime desc) as row_num
     from
         (
         select roleid as role_id, logtime, lev as level, occupation from `log_rolelogin` where logdate = '2024-05-28'
         union all
         select roleid as role_id, logtime, lev as level, occupation from `log_rolelogout` where logdate = '2024-05-28'
         union all
         select roleid as role_id, logtime, lev as level, occupation from `log_levelup` where logdate = '2024-05-28'
         ) as role_info
     ) as subquery
where row_num = 1
on duplicate key update level = values(level), occupation = values(occupation);

多次尝试无法设置别名,后来查找blog修改为:

on duplicate key update level = subquery.level, occupation = subquery.occupation;

执行结果似乎是正确的,但是看起来又哪里不对劲,有大佬帮忙解答一下这种方式是否正确,或者说结果正确的原因?谢谢

阅读 928
1 个回答
推荐问题
宣传栏