willboudle · January 31, 2020 19:27
diff --git a/move-gallery-img-to-main.sql b/move-gallery-img-to-main.sql
 UPDATE
  catalog_product_entity_varchar AS image_dest,
  (SELECT
    cpe.sku AS sku,
    cpe.row_id AS row_id,
    image.value AS main_image,
    gal.value AS galimage
  FROM
    catalog_product_entity cpe
    LEFT JOIN catalog_product_entity_varchar AS image
      ON cpe.row_id = image.row_id
      AND (
        image.attribute_id = 87
        OR image.attribute_id = 88
        OR image.attribute_id = 89
      )
      AND image.store_id = 0
    LEFT JOIN catalog_product_entity_media_gallery_value AS galval
      ON (cpe.row_id = galval.row_id)
    LEFT JOIN catalog_product_entity_media_gallery AS gal
      ON (galval.value_id = gal.value_id)
  WHERE image.value NOT LIKE CONCAT(
      '%',
      TRIM('-1' FROM SUBSTRING(cpe.sku, 4)),
      '%'
    ) # where image like sku with or without last "-1" as many images don't have it
     AND gal.value LIKE CONCAT(
      '%',
      TRIM('-1' FROM SUBSTRING(cpe.sku, 4)),
      '%'
    ) # where gallery image like sku with or without last "-1" as many images don't have it
     AND gal.value NOT REGEXP 'a[0-9].jpg' #not a secondary image a1, a2 etc)
  ) AS gallery_select
 SET
  image_dest.value = gallery_select.galimage
 WHERE image_dest.row_id = gallery_select.row_id
  AND (
    image_dest.attribute_id = 87
    OR image_dest.attribute_id = 88
    OR image_dest.attribute_id = 89
  )
  AND image_dest.store_id = 0;
	UPDATE
	catalog_product_entity_varchar AS image_dest,
	(SELECT
	cpe.sku AS sku,
	cpe.row_id AS row_id,
	image.value AS main_image,
	gal.value AS galimage
	FROM
	catalog_product_entity cpe
	LEFT JOIN catalog_product_entity_varchar AS image
	ON cpe.row_id = image.row_id
	AND (
	image.attribute_id = 87
	OR image.attribute_id = 88
	OR image.attribute_id = 89
	)
	AND image.store_id = 0
	LEFT JOIN catalog_product_entity_media_gallery_value AS galval
	ON (cpe.row_id = galval.row_id)
	LEFT JOIN catalog_product_entity_media_gallery AS gal
	ON (galval.value_id = gal.value_id)
	WHERE image.value NOT LIKE CONCAT(
	'%',
	TRIM('-1' FROM SUBSTRING(cpe.sku, 4)),
	'%'
	) # where image like sku with or without last "-1" as many images don't have it
	AND gal.value LIKE CONCAT(
	'%',
	TRIM('-1' FROM SUBSTRING(cpe.sku, 4)),
	'%'
	) # where gallery image like sku with or without last "-1" as many images don't have it
	AND gal.value NOT REGEXP 'a[0-9].jpg' #not a secondary image a1, a2 etc)
	) AS gallery_select
	SET
	image_dest.value = gallery_select.galimage
	WHERE image_dest.row_id = gallery_select.row_id
	AND (
	image_dest.attribute_id = 87
	OR image_dest.attribute_id = 88
	OR image_dest.attribute_id = 89
	)
	AND image_dest.store_id = 0;