CODEWITHSUNDEEP
mysql
Dan Wheeler
Dan Wheeler

Reputation: 1

sql select and delete from same table

I'm trying to run this sql query to clean up old data

DELETE FROM wp_postmeta 
where wp_postmeta.post_id=(
 SELECT post_id FROM wp_postmeta pm 
 WHERE (meta_key like '_form_id') AND 
 pm.post_id NOT IN (SELECT ID FROM wp_posts))

Getting the error

"Table 'wp_postmeta' is specified twice, both as a target for 'DELETE' and as a separate source for data"

What is the correct SQL?

Thanks.

Upvotes: 0

Views: 65

Answers (1)

Michael
Michael

Reputation: 5335

It seems that you're overcomplicating the problem, you don't need the subquery here. Also my guess is that you're missing the percent sign in LIKE.

 DELETE FROM wp_postmeta WHERE meta_key LIKE '%_form_id'
    AND post_id NOT IN (SELECT ID FROM wp_posts);

Upvotes: 1

Related Questions