Ask your WordPress questions! Pay money and get answers fast! (more info)

Need help with a MYSQL update statement WordPress

  • SOLVED

If I do the query:

SELECT wp_p2p.p2p_to, post_date from wp_p2p inner join wp_posts on wp_p2p.p2p_from = wp_posts.ID group by p2p_to order by min(wp_posts.post_date)

I get two columns wp_p2p.p2p_to and wp_posts.post_date

I need an update statement for wp_posts that does this:

UPDATE wp_posts set wp_posts.post_date = (post_date column from above) where wp_posts.ID = (the p2p_to column from above)

Answers (2)

2015-03-26

Francisco Javier Carazo Gil answers:

Katie,

Maybe a PHP loop is a better solution for this case.

Include subselects in a UPDATE sentence is not a good idea.


Francisco Javier Carazo Gil comments:

You can:

1) Create an array with wp_p2p.p2p_to as key and wp_posts.post_date as value
2) Do an loop, doing a update in SQL of every register in the array

If have any doubt tell me.


Katie comments:

I figured out a way thank you both for your help

2015-03-26

John Cotton answers:

I think this will work but you should test on some dummy data first.

UPDATE wp_posts
INNER JOIN wp_p2p ON wp_p2p.p2p_from = wp_posts.ID
SET wp_posts.post_date = wp_p2p.p2p_to


Katie comments:

I figured out a way thank you both for your help