请教大佬们一条数据库语句

先用 group by 算出每个 post_id 的 total views，然后 update views，最后删除 raw_views

update pm
set pm.meta_value = total_views
from post_meta pm
join
(
select post_id, sum(meta_value) as total_views
from post_meta
where meta_key in ('views', 'raw_views')
group by post_id
) as t
on pm.post_id = t.post_id
where pm.meta_key = views

delete from post_meta
where meta_key = 'raw_views'

@ra1983 首先感谢大佬的解答。
尝试了用大佬的方法，存在语法错误。

研究了一下，如果只是选择，可以把旧的 value 和新的 value 都取出来，这个没有问题。如下
select pm.meta_value, total_views
from `wp_postmeta` pm
join
(
select post_id, sum(meta_value) as total_views
from wp_postmeta
where meta_key in ('views', 'raw_pageviews')
group by post_id
) as t
on pm.post_id = t.post_id
where pm.meta_key = 'views';

但是加上 update ... set .. from 更新就报语法错误了
截图： https://wxt.sinaimg.cn/large/006RKGBpgy1ge80mmdp4bj30mj0d0aao.jpg
不知大佬能否再指点一下

@Kei001
把 subquery 变成 temporary table

可以发到 sql fiddle 上

数据不多随便写都可以，数据多了就不行。给你个例子，不担保没问题，你最好复制一个表出来测试确认对了再执行到业务表。

相加：

UPDATE `wp_postmeta` SET `meta_value` = IFNULL(`meta_value`, 0) + IFNULL((SELECT m2.`meta_value` FROM (select * from `wp_postmeta`) AS m2 WHERE m2.`post_id` = `wp_postmeta`.`post_id` AND m2.`meta_key` LIKE 'raw_views'), 0) WHERE `wp_postmeta`.`meta_key` = 'views'

删除：

DELETE FROM `wp_postmeta` WHERE `wp_postmeta`.`meta_key` = 'raw_views'

@Kei001 #2 “但是加上 update ... set .. from 更新就报语法错误了” 当然要报错了，你从哪儿学来的 update 接 from 的

update post_meta pm1
set pm1.meta_value = (
select sum(pm2.meta_value)
from post_meta pm2
where pm1.post_id = pm2.post_id
group by pm2.post_id )
where pm.meta_key = 'views';

UPDATE post_meta t1
SET ( meta_value ) = ( SELECT meta_value FROM post_meta WHERE post_id = t1.post_id and meta_key = 'raw_views') + t1.meta_value
where meta_key = 'views';
delete from post_meta where meta_key = 'raw_views'；

楼上的写法你用不了，估计是数据库的关系。要看你的数据库是啥，oracle 的话，可以用 merg into ...using ... on ..when matched then update 。mysql 估计 1 楼的就行

@ra1983 感谢大佬 搞定了

@eason1874 谢谢大佬指点 这条 SQL 非常好用

@shakoon 嘿嘿 初学者 谢谢大佬指点