string literals in query

Question

I am trying to hack together something in mySQL to allow me to change from one plugin to another without having to do manual data entry for all of my products on my woocommerce store.

The goal is to change every meta_value with the meta_key _woofv_video_embed from this format https://youtu.be/JCdljysorMo to this format a:1:{s:3:"url";s:28:"https://youtu.be/JCdljysorMo";} where every product has a different video link that needs to be updated.

I have:

update `wp_postmeta` set `meta_value`= concat( a:1:{s:3:"url";s:28:", meta_value, ";} ) where `meta_key` = '_woofv_video_embed'

I am trying to use the concatenate function but I need everything except for commas to be a string literal I tried:

update `wp_postmeta` set `meta_value`= concat( 'a:1:{s:3:"url";s:28:"', meta_value, '";'} ) where `meta_key` = '_woofv_video_embed'

but I still got an error. What do I need to do differently? Also will I need back ticks around meta_value inside the concatenate function? I am new to mySQL so I am just looking at other peoples code but wasn't able to find and example that worked for this specific instance. Any help would be greatly appreciated. I am running this script by using "SQL" within phpMyAdmin if that changes the answer at all.

Thank you

Answer 1

I misplaced a single quote. This query worked for me and solved my problem.

update wp_postmeta set meta_value= concat( 'a:1:{s:3:"url";s:28:"', meta_value, '";}' ) where meta_key = '_woofv_video_embed'

Answer 2

Never manipulate the DB directly, instead write some small wordpress plugin to do it. The amount of gotchas when trying to change a serialized data "by hand" is ridiculous and the risk do not justify the small amount of time it seems like it will save you.