How to delete entries from a SELECT query result in mysql?

If we have a complex query that returns the id value of rows in wp_posts that we want to delete (assuming that id is the primary key or a unique key of a row in the table)... as an example

SELECT p.id
  FROM wp_posts p
  JOIN wp_icl_translations t
    ON t.element_id = p.id 
 WHERE t.language_code = 'es-es'
   AND t.element_type  = 'post_product'
   AND p.post_type     = 'product'
 GROUP
    BY p.id
    

We can then use that query as an inline view. We wrap the query in parens and reference it in the FROM clause of another query. MySQL requires that we assign an alias to thhe inline view (or derived table in the MySQL vernacular).

We can join the result from the inline view that back to the table we want to remove rows from. We write this a SELECT statement first

SELECT r.*
  FROM ( -- inline view
         SELECT p.id
           FROM wp_posts p
           JOIN wp_icl_translations t
             ON t.element_id = p.id 
          WHERE t.language_code = 'es-es'
            AND t.element_type  = 'post_product'
            AND p.post_type     = 'product'
          GROUP
             BY p.id
       ) q
  JOIN wp_posts r
    ON r.id = q.id
    

to return the set of rows to be removed. We can verify that this is the intended set, or insert (create table as) the set of rows into backup...

Once we are confident that the SELECT is returning the rows we want to remove, we can convert it into a DELETE statement by replacing the SELECT keyword with DELETE.

DELETE r.*
  FROM ( -- inline view
         SELECT p.id
           FROM wp_posts p
           JOIN wp_icl_translations t
             ON t.element_id = p.id 
          WHERE t.language_code = 'es-es'
            AND t.element_type  = 'post_product'
            AND p.post_type     = 'product'
          GROUP
             BY p.id
       ) q
  JOIN wp_posts r
    ON r.id = q.id

Make sure the result has only one column wich you shall refer to when deleting data from the targetted tables. The delete queries will be equal to the number of tables you will require to delete from ie.

DELETE FROM table_1 where common_column in (YOUR_SELECT_QUERY);
DELETE FROM table_2 where common_column in (YOUR_SELECT_QUERY);
DELETE FROM table_3 where common_column in (YOUR_SELECT_QUERY);
DELETE FROM table_nth where common_column in (YOUR_SELECT_QUERY);

Your select query be like,

SELECT GROUP_CONCAT(temp_tbl.ID) FROM (SELECT wp_posts.* , wicl_translations.*
FROM wp_posts wp_posts join wp_icl_translations wicl_translations
ON (wicl_translations.element_id = wp_posts.ID)
WHERE (wicl_translations.language_code = 'es-es'
AND wicl_translations.element_type ='post_product'
AND wp_posts.post_type = 'product' ) GROUP BY wp_posts.ID) AS temp_tbl

You'r on the right track !

You just miss the correct WHERE condition :

DELETE FROM wp_posts WHERE wp_posts.ids IN (...)