Delete all posts from wp_posts table (and all associated tables) where a meta_value in wp_postmeta matches a certain value

Question

I have a WooCommerce store and I wish to run a MySQL Query to delete all wp_posts (orders) for a specific company, which is contained in a meta_key / meta_value pair in the wp_postmeta table.

What I've done so far

I've read this post: Mysql delete all posts that have a given meta_key which has been very useful and I've written this code:

delete a,b,c,d,e,f,g,h,i /* This bit tells us what to delete*/
FROM wp2_posts a
  LEFT JOIN wp2_term_relationships b ON ( a.ID = b.object_id )
  LEFT JOIN wp2_postmeta c ON ( a.ID = c.post_id )
  LEFT JOIN wp2_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
  LEFT JOIN wp2_terms e ON ( e.term_id = d.term_id )
  LEFT JOIN wp2_woocommerce_order_items f ON ( a.ID = f.order_id )
  LEFT JOIN wp2_woocommerce_order_itemmeta g ON ( f.order_item_id = g.order_item_id )
  LEFT JOIN wp2_comments h ON ( a.ID = h.comment_post_ID )
  LEFT JOIN wp2_commentmeta i ON ( h.comment_ID = i.comment_id )
WHERE a.ID IN /* This provides the args for deletion*/
  (   SELECT post_id
      FROM wp2_postmeta
      WHERE meta_key LIKE '_shipping_company'
      AND meta_value LIKE 'Acme Corp'
  )

Where I am struggling

But the response I get when trying to run a query in SQL pro is 'You can't specify target table 'c' for update in FROM clause'.

I assume it is saying that I can't delete something that I am using as an argument.

Any thoughts very much appreciated, Laura.

what does `a,b,c,d,e ..` suppose to be? aliases? then you want to delete all the tables? or just the rows ? (or partitions from the table -> https://dev.mysql.com/doc/refman/5.7/en/delete.html) — Edwin, Jan 22 '18 at 11:55
Hi Edwin, a, b, c, d, e is the alias assigned to each of the tables in the 'FROM' section. So wp2_posts is set as a. It is just shorthand. — LauraTheExplorer, Jan 22 '18 at 11:56
so if you want to delete all the tables, just use `drop`, but I guess you want just to delete all the post rows — Edwin, Jan 22 '18 at 11:57
try to replace `delete` with `select` and see if everything is as expected — Edwin, Jan 22 '18 at 12:34

astrangeloop · Accepted Answer · 2018-01-23T04:27:05.793

MySQL will not allow you to both modify and select from the same table at the same time. In this case, you are running a DELETE and a SELECT against the wp2_postmeta table.

There are many approaches to resolving this (see for example MySQL Error 1093 - Can't specify target table for update in FROM clause) but in this case, you don't need the subquery in the WHERE clause, as you can check the meta_key and meta_value columns of the wp2_postmeta table directly. Removing the SELECT statement in the subquery solves the issue:

DELETE c,a,b,d,e,f,g,h,i
FROM wp2_postmeta c
JOIN wp2_posts a ON ( a.ID = c.post_id )
LEFT JOIN wp2_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp2_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp2_terms e ON ( e.term_id = d.term_id )
LEFT JOIN wp2_woocommerce_order_items f ON ( a.ID = f.order_id )
LEFT JOIN wp2_woocommerce_order_itemmeta g ON ( f.order_item_id = g.order_item_id )
LEFT JOIN wp2_comments h ON ( a.ID = h.comment_post_ID )
LEFT JOIN wp2_commentmeta i ON ( h.comment_ID = i.comment_id )
WHERE c.meta_key LIKE '_shipping_company'
AND c.meta_value LIKE 'Acme Corp'

Changes from original query:

start with wp2_postmeta first
join to wp2_posts second
filter by meta_key and meta_value directly in WHERE clause

Delete all posts from wp_posts table (and all associated tables) where a meta_value in wp_postmeta matches a certain value

1 Answers1