CODEWITHSUNDEEP
mysqlsqlwordpresssql-update
MillerMedia
MillerMedia

Reputation: 3671

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
SET
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!

Upvotes: 0

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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';

Upvotes: 1

Related Questions