I am working on a wordpress site where I need to change the upload path for all attachment images (I have no clue why the client initially selected to not have them organized.)
This is what I am running to select my attachments, and grab what the parent posts’ post date was so I can correctly grab only what I want.
select a.id as child_post_id, a.guid as upload_path, b.post_date as parent_post_date
from wp_posts a
left outer join wp_posts b
on a.post_parent = b.id
where
a.post_type = 'attachment'
and b.post_date between '2015-11-17 00:00:00' and '2015-11-17 23:59:59'
Which pulls up the 8 or so image attachments I am looking for in that span as a test and they are paired with the parent posts post id, so I know I am getting what I want.
Now I want to replace a portion of the value in the guid column, so I am doing a replace in the outputted column of “upload_path”
update upload_path
set upload_path = replace(upload_path, 'wp-content/uploads/', 'wp-content/uploads/2015/11/')
;
This will cause a failure of syntax and I feel like I’m pulling an odd manuever so searching for an answer has been difficult.
I’m not super experienced going deeper in SQL, but I’m trying to learn.
Do I need to save this query and then run an update on that saved query?
This is probably an easy fix, can someone please help me out- just point me in the right direction, please.
Running mysql 5.1.73 (we have to run what the production server is running)
So it turns out, I was trying to run an update within a select statement. Which is a no-go.
I had to run an update statement and then define the inner join and declare each “where” as a limiter within that command. using the “and” syntax.
Wonderful learning experience. Thanks to all that took a look.