[FIXED] How to Set a Field within the results set of this SELECT statement?

Issue

So I have an SQL query, which I am running on a Magento 2 database. It successfully lists All items where the 'status' is 'disabled' (2), and has a stock qty greater than 0.

select
  `eav_attribute`.`attribute_id` AS `attribute_id`,
  `catalog_product_entity_int`.`entity_id` AS `entity_id`,
  `catalog_product_entity_int`.`value` AS `value`,
  `eav_attribute`.`attribute_code` AS `attribute_code`,
  `catalog_product_entity`.`sku` AS `sku`,
  `catalog_product_entity`.`type_id` AS `type_id`,
  `cataloginventory_stock_item`.`qty` AS `qty`

from
  (((`eav_attribute`
  join `catalog_product_entity_int` on ((`eav_attribute`.`attribute_id` = `catalog_product_entity_int`.`attribute_id`)))
  join `catalog_product_entity` on ((`catalog_product_entity_int`.`entity_id` = `catalog_product_entity`.`entity_id`)))
  join `cataloginventory_stock_item` on ((`catalog_product_entity_int`.`entity_id` = `cataloginventory_stock_item`.`product_id`)))


where
  ((`eav_attribute`.`attribute_code` = 'status') and
  (`catalog_product_entity_int`.`value` = 2)) and
  (`cataloginventory_stock_item`.`qty` > 0 )

It works for selecting the result set and provides me with an accurate list of items that meet those 2 criteria. How would I amend this to set the 'status' of the items in this result set to 'Enabled' (1). So essentially, I just need these criteria to run, then for every one of the results, set the column catalog_product_entity_int.value to 1 instead of 2.

Solution

You can use update:

update eav_attribute ea join
       catalog_product_entity_int cpei
       on ea.attribute_id = cpei.attribute_id join
       catalog_product_entity cpe
       on cpei.entity_id = cpe.entity_id join
       cataloginventory_stock_item cisi
       on cpei.entity_id = cisi.product_id
    set cpei.value = 2
    where ea.attribute_code = 'status' and
          cpei.value = 2 and
          ci.qty > 0 ;

Note that I added table aliases and removed parentheses and backticks -- so the query is more easily written and read.

Answered By - Gordon Linoff

Answer Checked By - David Goodson (PHPFixing Volunteer)