Magento SQL query to set a base image for any products that do not have one set.
I had to do some work on a Magento site today, where the site admin had added images to all the products, but had not set any of them as base, small, or thumb images - so none of the listings were showing an image on the front end. I googled for a SQL query to do the job, and found none - so I knocked this one up - maybe someone else will find it useful.
The queries do not affect any products that already have the respective values already set.
Base Images:
INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) SELECT 4, 79, 0, cpev.entity_id, cpemg.value FROM catalog_product_entity_varchar AS cpev INNER JOIN catalog_product_entity_media_gallery AS cpemg ON cpev.entity_id = cpemg.entity_id WHERE cpev.entity_id NOT IN (SELECT entity_id FROM catalog_product_entity_varchar WHERE entity_type_id = 4 AND attribute_id = 79 AND value IS NOT NULL AND value != 'no_selection')
Small Images:
INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) SELECT 4, 80, 0, cpev.entity_id, cpemg.value FROM catalog_product_entity_varchar AS cpev INNER JOIN catalog_product_entity_media_gallery AS cpemg ON cpev.entity_id = cpemg.entity_id WHERE cpev.entity_id NOT IN (SELECT entity_id FROM catalog_product_entity_varchar WHERE entity_type_id = 4 AND attribute_id = 80 AND value IS NOT NULL AND value != 'no_selection')
Thumb Images:
INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) SELECT 4, 81, 0, cpev.entity_id, cpemg.value FROM catalog_product_entity_varchar AS cpev INNER JOIN catalog_product_entity_media_gallery AS cpemg ON cpev.entity_id = cpemg.entity_id WHERE cpev.entity_id NOT IN (SELECT entity_id FROM catalog_product_entity_varchar WHERE entity_type_id = 4 AND attribute_id = 81 AND value IS NOT NULL AND value != ‘no_selection’)
Hopefully this can save someone else a bit of time!