MillerMedia MillerMedia - 2 years ago 89
SQL Question

MySQL - Update Rows with Selected Content

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
wp.post_content =
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"

The error I get is:

Every derived table must have its own alias.

I'm using aliases on everything in the select query as far as I can tell so I must just be missing something small. I looked up some other similar questions but none of the answers dealt with a select query nested in an update query so they weren't of much help.

Any help would be greatly appreciated. Thanks!

Answer Source

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 = 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';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download