I'm trying to do a bulk update of some columns in a MySQL DB. This is a WordPress/WooCommerce site so some of these columns may look familiar.
Basically what I'm trying to do is take the content from the post_content column in the wp_posts table, concatenate it with that post ID's meta value of '_sku' and then reupdate that some post_content field.
I think I'm close. I have the select query working fine but now I'm just trying to wrap it in an update query. Here's what I have:
UPDATE wp_posts as wp
SELECT concat(wp2.post_content,' (SKU:',wpmeta.meta_value,')') as new_content FROM (SELECT * FROM `wp_posts` as wp2) INNER JOIN `wp_postmeta` as wpmeta ON wp2.ID=wpmeta.post_id WHERE wp2.post_type = 'product' AND wpmeta.meta_key = "_sku"
Every derived table must have its own alias.
Your logic is a bit hard to follow. But, you should be doing this with a
join in the
update. Something like this:
UPDATE wp_posts wp JOIN wp_postmeta pm ON wp.id = pm.post_id AND pm.meta_key = '_sku' SET wp.post_content = CONCAT(wp.post_content, ' (SKU:', pm.meta_value, ')') WHERE wp.post_type = 'product';