If a meta_key has a meta_value, update the meta_key name

In a table called wp_postmeta, there are ~1K meta_key‘s called _wcj_custom_product_tabs_content_local_1, but only ~100 have a meta_value.

What I need to do is update the meta_key name, but only for the meta_key‘s that actually have a meta_value.

I can select all the meta_key‘s that are not empty with this:

SELECT DISTINCT meta_value 
FROM wp_postmeta 
WHERE meta_key LIKE '_wcj_custom_product_tabs_content_local_1' 
AND meta_value IS NOT NULL
AND meta_value != ''

But then how do I combine the query above with the update query below so only the not empty meta_keys selected have their names updated?

update wp_postmeta 
set meta_key = 'custom_product_tab_content' 
where meta_key = '_wcj_custom_product_tabs_content_local_1' 

Answer

Taking the WHERE clauses from your SELECT query, and prepending the UPDATE statement gives you the following query:

UPDATE wp_postmeta 
SET meta_key = 'custom_product_tab_content' 
WHERE meta_key = '_wcj_custom_product_tabs_content_local_1'
AND meta_value IS NOT NULL
AND meta_value != ''