mysql - 数据库查询失败示例；你能帮我解决问题吗？

Question

我正在尝试更新名为“dogs_postmeta”的 Wordpress 数据库表。下面是我的查询，它非常适合显示所有适当的数据。

select meta_value as field_i_want_to_change, i.url as data
from dogs_postmeta
inner join (select a.post_id, CONCAT('https://mydomain/wp-content/uploads/', b.meta_value) as url
from
dogs_postmeta a, dogs_postmeta b
where
a.meta_key = '_thumbnail_id' and
b.post_id = a.meta_value and
b.meta_key = '_wp_attached_file'
) i
on i.post_id = dogs_postmeta.post_id
where
dogs_postmeta.meta_key = "_seopress_pro_rich_snippets_article_img"
limit 100;

我想使用上述“数据”属性更新现有字段 - meta_value。我已经这样做了几个小时但没有成功。

这是我尝试过的。

update dogs_postmeta
set dogs_postmeta.meta_value = i.url
from (select a.post_id, CONCAT('https://mydomain/wp-content/uploads/', b.meta_value) as url
from
dogs_postmeta a, dogs_postmeta b
where
a.meta_key = '_thumbnail_id' and
b.post_id = a.meta_value and
b.meta_key = '_wp_attached_file'
) i
inner join dogs_postmeta on dogs_postmeta.post_id = i.post_id 
where
dogs_postmeta.meta_key = "_seopress_pro_rich_snippets_article_img";

我收到此错误消息 -

Error in query (1064): Syntax error near 'from (select a.post_id, CONCAT('https://mydomain/wp-content/uploads/', b.meta_va' at line 3

...但我确信这不是问题所在。

谁能帮我解决这个问题吗？

Answer 1

update dogs_postmeta as d
inner join (select a.post_id,  b.meta_value as url
from
dogs_postmeta a, dogs_postmeta b
where
a.meta_key = '_thumbnail_id' and
b.post_id = a.meta_value and
b.meta_key = '_wp_attached_file'
) as i on d.post_id = i.post_id 
set d.meta_value = CONCAT('https://mydomain/wp-content/uploads/', i.url)
where
d.meta_key = "_seopress_pro_rich_snippets_article_img";