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

Question

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

Hi interesting, perhaps this syntax will be of interest https://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql — IronMan, Jul 21 '20 at 18:53
@IronMan thanks, I actually tried that as well but I am probably doing something wrong and can't figure out the right syntax for this . It's a long query ! — Optimus Servers, Jul 21 '20 at 18:55
Wel, your where clause does not make any sense because the complex subquery returns bunch of records only, but there is no criteria as to which records should be deleted — Shadow, Jul 21 '20 at 18:59
@Shadow I think I made it pretty clear I want to delete all of the results of my query ? — Optimus Servers, Jul 21 '20 at 19:04
Yeah, you made it clear tò me and other humans, but not to mysql. The where clause needs to evaluate to a true / false result for each record, otherwise mysql will not know which rows to include. — Shadow, Jul 21 '20 at 19:07
I think I understand what you're trying to say but I tried adding a WHERE condition at the end in order to delete all entries (used post_author=1 in this case because all are from same author) and still no go . — Optimus Servers, Jul 21 '20 at 19:21

score 2 · Accepted Answer · answered Jul 21 '20 at 20:01

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

Thanks so much, I have done some research on this subject before and this is by far the best pratical example I have seen on how to manage situations like this ! Worked brilliantly with the added bonus of being able to insert rows and backup ! — Optimus Servers, Jul 21 '20 at 20:11
(This doesn't backup rows that get deleted by CASCADE of foreign key constraints. That's extra steps, but still based on the SELECT. I *always* write a one-off DELETE statement as a SELECT statement first, then I can verify the set of rows to be removed. Maybe not as important with simple criteria, but the more complex WHERE and JOIN crtieria... I'd much rather find the problem in a SELECT than deal with the fallout of inadvertently removing rows that shouldn't have been removed. — spencer7593, Jul 21 '20 at 20:44

score 0 · Answer 2 · answered Jul 21 '20 at 18:57

0

You'r on the right track !

You just miss the correct WHERE condition :

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

answered Jul 21 '20 at 18:57

Orkad

630
5
15

Thanks but sadly that didn't work : Error in query (1241): Operand should contain 1 column(s) After adding this in the beginning : DELETE FROM wp_posts WHERE wp_posts.ID IN ( – Optimus Servers Jul 21 '20 at 19:04
Yea of course sorry for that. In your subquery, you have to select wp_posts.ids only : SELECT wp_posts.ids ... GROUP BY wp_posts.ids – Orkad Jul 21 '20 at 19:07

score 0 · Answer 3 · answered Jul 21 '20 at 19:41

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

Tyvm Alberto, that seems to be closer for sure but somehow it's only affecting one row at a time ! I added this condition : DELETE FROM wp_posts where ID in (yourqueryhere); It runs without errors but only 1 row affected each time ? — Optimus Servers, Jul 21 '20 at 20:06

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

3 Answers3