CODEWITHSUNDEEP
mysqlsqlmagento
Matt
Matt

Reputation: 31

Magento disable products with no images

I am trying to disable a lot of products that do not have images in my Magento installation.

The following SQL query is supposed to get all products with no images but I need a way to set all products with no images to status disabled?

SELECT * FROM catalog_product_entity_media_gallery
        RIGHT OUTER JOIN catalog_product_entity
            ON catalog_product_entity.entity_id = talog_product_entity_media_gallery.entity_id 
   WHERE catalog_product_entity_media_gallery.value is NULL

Upvotes: 0

Views: 2603

Answers (4)

Andrey Inyagin
Andrey Inyagin

Reputation: 31

I use this for magento 2.2.3

update catalog_product_entity_int m
left join eav_attribute a on a.entity_type_id = 4 and a.attribute_id = m.attribute_id
set value = 2
where
  a.attribute_code = 'status'
  and m.entity_id in
    (
      select m.entity_id
      from catalog_product_entity m
      left join catalog_product_entity_media_gallery_value_to_entity a
        on a.entity_id = m.entity_id
      where a.value_id is null
    )
;

Upvotes: 3

Aneesh
Aneesh

Reputation: 449

I have modified it slightly and this worked for me

Removed WHERE attribute_id = 4 and updated SELECT entity_id to SELECT catalog_product_entity.entity_id

UPDATE catalog_product_entity_int SET value = 2 
WHERE entity_id IN(
        SELECT catalog_product_entity.entity_id 
        FROM catalog_product_entity_media_gallery
        RIGHT OUTER JOIN catalog_product_entity ON catalog_product_entity.entity_id = catalog_product_entity_media_gallery.entity_id 
        WHERE catalog_product_entity_media_gallery.value is NULL
    );

Upvotes: 0

medina
medina

Reputation: 8159

Here is what you are looking for Kode:

-- here you set every one as DISABLED (id 2)
UPDATE catalog_product_entity_int SET value = 2 
-- here you are change just the attribute STATUS
WHERE attribute_id = 4
    -- here you are looking for the products that match your criteria
    AND entity_id IN (
        -- your original search
        SELECT entity_id 
        FROM catalog_product_entity_media_gallery
        RIGHT OUTER JOIN catalog_product_entity ON catalog_product_entity.entity_id = catalog_product_entity_media_gallery.entity_id 
        WHERE catalog_product_entity_media_gallery.value is NULL
    );

Upvotes: 2

liyakat
liyakat

Reputation: 11853

This will obviously apply to any other attribute, so just change the code as necessary.

enabled = 1
disabled = 2

UPDATE catalog_product_entity_int cpei, catalog_product_entity cpe
SET cpei.value = "2"
WHERE cpe.entity_id = cpei.entity_id
AND cpe.sku LIKE '%SKU%'
AND cpei.attribute_id = 273

Also you can refer detail Document link

let me know if i can help you further

Upvotes: 0

Related Questions