Just found the solution after a long time of trying-
UPDATE wp_posts p
JOIN wp_term_relationships r
ON r.object_id = p.id
JOIN wp_terms t
ON r.term_taxonomy_id = t.term_id
JOIN wp_term_taxonomy x
ON r.term_taxonomy_id = x.term_taxonomy_id
SET p.post_content = CASE
WHEN t.name="ACategory" THEN REPLACE(post_content, 'http://sitename.com/FileID/filename.zip',
'http://sitename.com/FileID/ACategory-filename.zip')
WHEN t.name="BCategory" THEN REPLACE(post_content, 'http://sitename.com/FileID/filename.zip',
'http://sitename.com/FileID/BCategory-filename.zip')
ELSE post_content
END
WHERE x.taxonomy = 'category'
Look closely, I made some changes to your code. And give special attention to the CASE
command which I included to the code. For each category name add an extra WHEN t.name="Your-Category-Name" THEN REPLACE(post_content, 'http://sitename.com/FileID/filename.zip', 'http://sitename.com/FileID/new-filename.zip')
.
Hope that helps.
I tested it and it worked perfectly in my system.