I currently have a site that has a number of custom post types. I want to ideally have the following structure /wp-content-uploads/posttype/postid/xxxx.jpg. All the images are attachments of the posts themselves

I have a script to move the current images by using the following MySQL

SELECT p.id, p.post_type, p.guid, p.post_parent, m.meta_id, m.meta_key, m.meta_value, p2.post_type as parent_type
FROM wp_posts AS p
INNER JOIN wp_postmeta AS f ON f.post_id = p.id AND f.meta_key = '_wp_attached_file'
INNER JOIN wp_postmeta AS m ON m.post_id = p.id AND m.meta_key = '_wp_attachment_metadata'
INNER JOIN wp_posts AS p2 ON p2.id = p.post_parent
WHERE p.post_type = 'attachment'
AND p2.post_type IN('customposttype1','customposttype2','customposttype3')

With this I get all the relevant info in order to create the various folders and move the images, so this side of things is not an issue.

The issue I have is whilst I run the script and the images are moved, the post still references the old images ie wp-content/uploads/xxx.jpg.

Once I have moved the images I am updating the images guid in the wp_posts table, I figured this would be where the image location is stored, but now I am guessing is that wordpress uses a fixed uploads structure depending on what is selected in the backend rather than storing the location in the database.

Can someone confirm if this is the case or what table stores the actual path to the image that I could change in my script for the images to appear as expected.

I have backed up the database and looked at the file and I see the images referenced in a few places, but any full paths appear in the format that I have supplied.