CODEWITHSUNDEEP
mysqlsql
user2389087
user2389087

Reputation: 1762

SQL select ID then use that ID in an update

I have two simple sql statements, both work independently but I would like to chain them together. I only have the SKU of the product so trying to grab the post_id from SKU. Then update stock qty based on post_id

This grabs post_id

Select post_id from wp_postmeta
where meta_value = 'xxxxxxx'

This updates stock

UPDATE wp_postmeta
SET meta_value = 1000
WHERE meta_key = '_stock'
AND post_id = RESULT FROM SELECT

I can't just update stock via sku as woocommerce stores the sku and stock both as meta_key and meta_value.

Looked around and saw a lot of things around updating selected rows, but i'm not trying to achieve that, i'm trying to use the result of the select in the update.

Alternatively is this even an applicable application for SQL, I've got this working but just returning the result of the select, storing as variable in php and then using in an update statement. Maybe that is correct architecturally?

Upvotes: 1

Views: 1610

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

In most databases you would simply use the standard SQL syntax:

UPDATE wp_postmeta
    SET meta_value = 1000
    WHERE meta_key = '_stock' AND
          post_id IN (SELECT post_id
                      FROM wp_postmeta
                      WHERE meta_value = 'xxxxxxx'
                     );

The one database where this doesn't work is MySQL, which is probably the one you are using. For that, you need an explicit JOIN:

UPDATE wp_postmeta pm JOIN
       wp_postmeta pm2
       ON pm.post_id = pm2.post_id AND
          pm2.meta_value = 'xxxxxxx'
    SET meta_value = 1000
    WHERE pm.meta_key = '_stock' ;

Upvotes: 5

Related Questions