TransWikia.com

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

Stack Overflow Asked by Optimus Servers on November 12, 2021

I have the following SQL query :

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

This returns all the results I need to delete from my database so I have tried several DELETE queries but getting syntax errors in all of them .

Example :

DELETE FROM wp_posts
WHERE (



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 = 'pt-pt'
AND wicl_translations.element_type ='post_product'
AND wp_posts.post_type = 'product' ) GROUP BY wp_posts.ID
)
);

Also tried this :

DELETE FROM wp_posts WHERE wp_posts.ID = ANY IN (

SELECT wp_posts.ID, 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
)

It`s a complex aggregated query and I lack the mysql knowledge to properly write a rule for deleting these results .

How could I approach this ?

Thanks

3 Answers

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

Answered by spencer7593 on November 12, 2021

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

Answered by Albert Alberto on November 12, 2021

You'r on the right track !

You just miss the correct WHERE condition :

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

Answered by Orkad on November 12, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP