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)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.