In MySQL, you can’t modify the same table which you use in the SELECT part.
The following SQL will return the error mentioned in the title.
UPDATE wp_posts SET post_status = 'publish' WHERE id IN (SELECT id FROM `wp_posts` WHERE post_status = 'future')
Solution
The solution is simple. In this case, all you need to is introduce another subquery, as such (I hope you can spot the difference):
UPDATE wp_posts SET post_status = 'publish' WHERE id IN (SELECT id from (SELECT id FROM `wp_posts` WHERE post_status = 'future') as tmptable)
* Tested this on a wordpress site and updated 800+ rows of posts in 0.2267 sec (using phpMyAdmin)
Eugene says
The simplest and easiest solution. Just duplicate select statement. Thanks
Ali says
Worked for me – I tried a few methods that I found on Stackoverflow – none worked in my scenario. I wanted to copy the value of one column in a particular row into the same column of another row. Here is what my query (might help someone someday)
update `2c9s5X2oC_postmeta` set meta_value = (select val from (select meta_value as val from `2c9s5X2oC_postmeta` where post_id = 3609 and meta_key = ‘_schedule_end’) as tmptable)
WHERE `post_id` = 3609 and `meta_key` = ‘_schedule_next_payment’