3 posts tagged

sql

Un-cancel Magento Order

If you want to restore canceled order in Magento and Magento 2 use the following SQL query. Order will be restored to “Pending” status.

SET @ORDERID = "41970";
UPDATE `sales_order`
SET `state` = 'new',
`status` = 'pending',
`base_discount_canceled` = '0',
`base_shipping_canceled` = '0',
`base_subtotal_canceled` = '0',
`base_tax_canceled` = '0',
`base_total_canceled` = '0',
`discount_canceled` = '0',
`shipping_canceled` = '0',
`subtotal_canceled` = '0',
`tax_canceled` = '0',
`total_canceled` = '0'
WHERE `entity_id` = @ORDERID;
UPDATE `sales_order_item`
SET `qty_canceled` = '0',
`tax_canceled` = '0',
`discount_tax_compensation_canceled` = '0'
WHERE (`order_id` = @ORDERID)
;
Aug 20   mysql   order   sql

Find Products Without Images

SELECT `p`.`sku` FROM `catalog_product_entity` AS `p`
LEFT JOIN `catalog_product_entity_varchar` AS `attr_images`
	ON `attr_images`.`entity_id` = `p`.`entity_id`
	AND `attr_images`.`attribute_id` IN (SELECT `attribute_id` FROM `eav_attribute`
			WHERE `attribute_code` IN ('image', 'small_image', 'thumbnail') AND `entity_type_id` = 4
		)
LEFT JOIN `catalog_product_entity_media_gallery` AS `gallery`
	ON `gallery`.`entity_id` = `p`.`entity_id`

WHERE (`attr_images`.`value` IS NULL OR `attr_images`.`value` = 'no_selection') AND (`gallery`.`value` IS NULL);
2019   gallery   images   product   sql

Hide all categories without products and show categories with products

Reindex category products index at first and then execute following sql-script.

UPDATE `catalog_category_entity_int` AS `status`
INNER JOIN `eav_attribute` AS `attr` ON `attr`.`attribute_code` = 'is_active'
AND `attr`.`entity_type_id` = 3
AND `status`.`attribute_id` = `attr`.`attribute_id`
SET `status`.`value` = IF((SELECT COUNT(`index`.`product_id`)
	FROM `catalog_category_product_index` AS `index`
	WHERE `index`.`category_id` = `status`.`entity_id` GROUP BY `index`.`category_id`) > 0, 1, 0)
WHERE `status`.`store_id` = 0
2016   category   product   sql