0

i have a select query that check for duplicates.

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

this returns 2458 records with count(meta_value) greater than 1-- i need to delete these records (leaving one copy of the record if possible) but everytime i try a delete query like this i get an error

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)

error is

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

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
ForgivenIT
  • 169
  • 13

1 Answers1

0

i think you just missed an operator there:

delete p FROM wp_yepf_posts p join wp_yepf_postmeta pm on p.ID = pm.post_id AND (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)

Ben Yitzhaki
  • 1,376
  • 16
  • 31
  • Err] 1093 - You can't specify target table 'p' for update in FROM clause – ForgivenIT Jun 22 '17 at 16:23
  • I think you got a pretty straight forward error now. would suggest you to try and use different aliases (you used "p" twice). also, this seems to be possibly a duplicate of https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause so it might help you out – Ben Yitzhaki Jun 22 '17 at 16:31
  • i know how to delete where in and not in ( what i cant get to work is delete duplicates...not trying to be dense but i am missing something somewher – ForgivenIT Jun 22 '17 at 16:52