How to use query result as REPLACE parameters

Question

i'm trying to:

1- Find a string inside a lot of content

2- Replace that specific part of the string with a new one using REPLACE function like this:

REPLACE(column_name, query#1, query#2)

Is this possible?

I already have located the exact part of the content that i want to replace

SELECT `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts_duplicatedfortestonly WHERE `post_content` LIKE '%[flv:%'

and have the new string ready in another table column: Png_Link

Finally i'm trying to merge all this using the REPLACE function but it's giving me a lot of errors:

UPDATE wp_posts_duplicatedfortestonly 
SET 
    post_content = REPLACE(post_content,
                           (SELECT SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM (SELECT post_content FROM wp_posts_duplicatedfortestonly) AS something WHERE `post_content` LIKE '%[flv:%'),
                           (SELECT `Png_Link`  FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != ''));

Something like this (only difference is i'm using 2 tables): https://www.db-fiddle.com/f/91EW2kJDc8aWJYYvw86vG9/0

Also tried with variables without any luck:

SET @tblname := "wp_posts_duplicatedfortestonly";
SET @colname := "post_content";
SELECT @lookfor := SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) FROM @tblname WHERE `post_content` LIKE '%[flv:%';
SELECT @replacewith := `Png_Link` FROM `VideoPlayerUdpateLinksReady` WHERE `Mp4_Link` != '';

SET @qry = CONCAT('UPDATE ',@tblname, ' SET ', @colname, ' = REPLACE(' ,@colname, ',', @lookfor, ',',@replacewith,')');
PREPARE QUERY FROM @qry;
EXECUTE QUERY;

What am i'm doing wrong? Is it possible to use SELECT expressions as REPLACE function parameters or what other workaround do i have?

Answer 1

Managed to solve it on my own rookie way:

1- Since i couldn't make the REPLACE function to accept a query as parameter i just inserted the query result in a new table column:

INSERT INTO VideoPlayerUdpate_FLV_links (`ID`,`PostTitle`,`AmazonLinks`) SELECT `ID`, `post_title`, SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`) - LOCATE('[flv:',`post_content`) + 1) AS amazonlinks FROM wp_posts WHERE `post_content` LIKE '%[flv:%'

Remember I had another table VideoPlayerUdpateLinksReady with all my new string that i wanted to replace. I had to manually construct and validate all errors in this column VideoPlayerUdpateLinksReady.Png_Link so it would be ready to replace.

2- So final Step was only to use these 2 tables as parameters in the REPLACE function:

UPDATE `wp_posts_duplicatedfortestonly`  JOIN `VideoPlayerUdpate_FLV_links` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpate_FLV_links.ID  JOIN `VideoPlayerUdpateLinksReady` ON wp_posts_duplicatedfortestonly.ID = VideoPlayerUdpateLinksReady.ID SET `post_content` = REPLACE(`post_content`,VideoPlayerUdpate_FLV_links.AmazonLinks, VideoPlayerUdpateLinksReady.Png_Link)

And i got all my content replaced without any further issues.