mysql - 通过使用 join 语句的查询删除行

Question

我有一个选择查询来检查重复项。

select ID, post_id, post_name, post_date, Count(meta_value) FROM wp_yepf_posts p join wp_yepf_postmeta pm on p.ID = pm.post_id WHERE p.post_type = 'product' and pm.meta_key='_sku'  group by meta_value having count(meta_value)>1 

这将返回 2458 条记录，其中 count(meta_value) 大于 1 - 我需要删除这些记录(如果可能的话，保留一份记录副本)，但每次我尝试这样的删除查询时，我都会收到错误

delete p 
FROM wp_yepf_posts p 
join wp_yepf_postmeta pm on p.ID = pm.post_id (select DUPLICATE.entity_id in (Select DISTINCT(pm.meta_key='_sku') as sku, Count(pm.meta_key='_sku') as skuCount, entity_id
from wp_yepf_posts p join wp_yepf_postmeta pm on p.ID = pm.post_id 
group by sku Having skucount>1) as dup)

错误是

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select DUPLICATE.entity_id in (Select DISTINCT(pm.meta_key='_sku') as sku, Count' at line 1

Answer 1

我想你只是错过了一个运算符(operator):

删除p来自 wp_yepf_posts p加入 wp_yepf_postmeta pm on p.ID = pm.post_id AND (在 (Select DISTINCT(pm.meta_key='_sku') as sku, Count(pm.meta_key='_sku') 中选择 DUPLICATE.entity_id作为 skuCount、entity_id来自 wp_yepf_posts p 加入 wp_yepf_postmeta pm on p.ID = pm.post_id按 sku 分组 skucount>1) 作为 dup)