如何在PHPMyAdmin MySQL的同一个表中将值从一行复制到另一行

Question

STEP 1

We have a table called wp_postmeta with a meta_key being video_url

We have another meta_key value in the same table being _heap_video_embed

We want to copy all the values from the video_url field into the _heap_video_embed value, ensuring that the post_id values still correspond to the correct posts

STEP 2

The values in video_url are all youtube links e.g. http://www.youtube.com/watch?v=ZHIhsLhQ-q8

However, once these have been copied into heap_video_embed we want to change this to use the youtube embed code also replacing everything before the = to //www.youtube.com/embed/

<iframe width="640" height="360" src="VIDEO_URL_HERE" frameborder="0" allowfullscreen></iframe>

Answer 1

Something like this should do the tick

UPDATE table_name SET _heap_video_embed=CONCAT('<iframe width="640" height="360" src="',REPLACE(video_url, 'http://www.youtube.com/watch?v=', 'http://www.youtube.com/embed/'),'" frameborder="0" allowfullscreen></iframe>');

changed

http://www.youtube.com/watch?v=ZHIhsLhQ-q8

into

<iframe width="640" height="360" src=" http://www.youtube.com/embed/ZHIhsLhQ-q8" frameborder="0" allowfullscreen></iframe>

To be honest no idea where the extra space before the http came from and if this is exactly what you wanted or that the url should not have http infront but you should be able to adapt this to your needs.