MySQL Multiple Select Statements With An Update

Question

I need to select the cost value of a product_id and then update the lowest value of that product_id

I need no merge these two select statements, then run an update. I need to get the cost from one field and then multiple the formula to set the lowest price I can sell for.

##COST
SELECT value AS cost from catalog_product_entity_decimal
  where attribute_id = 79 and
        entity_id IN (select product_id from catalog_category_product)

##LOWEST
SELECT value AS lowest from catalog_product_entity_decimal
  where attribute_id = 164 and
        entity_id IN (select product_id from catalog_category_product)


update catalog_product_entity_decimal
   set value = (cost+3.5)/.84
   where attribute_id = 164 and
         entity_id IN (select product_id from catalog_category_product);

How would I go about doing this? I'm a MySQL novice to say the least.
I've tried a UNION and Multiple Selects, but I guess I've done it wrong.

Community · Accepted Answer · 2017-05-23 12:29:27Z

You can use a SELECT as sub-query to the UPDATE statement:

UPDATE catalog_product_entity_decimal
SET value = ( (
    SELECT value
    FROM catalog_product_entity_decimal
    WHERE attribute_id = 79
    AND entity_id IN (select product_id from catalog_category_product)
) + 3.5 ) / .84

Or you can store the result from each select in a user-defined variable (@cost):

SELECT @cost := `value`
FROM catalog_product_entity_decimal
WHERE attribute_id = 79
AND entity_id IN (select product_id from catalog_category_product);

UPDATE catalog_product_entity_decimal
SET value = ( @cost + 3.5 ) / .84;

See also: MySQL: Set user variable from result of query

A difference between both methods is that the first one is one query while the method use two distinct queries. But both need o be executed one after the other to ensure that the variable is not overwritten by another concurrent SELECT/UPDATE operation from another request.

(Where do you use lowest?)

Lowest attribute id is 164 it's the one I want to update, not cost. I need to get the cost, then do the math to update lowest
Unless I'm wrong, I don't think your methods are going to work. I need to select the cost value of a product_id and then update the lowest value of that product_id.

Collectives™ on Stack Overflow

MySQL Multiple Select Statements With An Update

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related