Magento query master

This post is collection of mysql query to perform particular task, this will make you life easy.

NOTE : Basically this is not good practis to work with database directlly for live project. Make sure that you have database back up before run query.

Update customer group of all subscriber customer using query – Magento

UPDATE
    customer_entity,
    newsletter_subscriber
SET
    customer_entity.`group_id` = 5
WHERE
    customer_entity.`entity_id` = newsletter_subscriber.`customer_id`
AND
    newsletter_subscriber.`subscriber_status` = 1;

Turn template hints on/off using query – Magento

UPDATE
   core_config_data
SET
   value = 0
WHERE
   path = "dev/debug/template_hints"
OR
   path = "dev/debug/template_hints_blocks";

Change admin user password using query – magento

UPDATE admin_user SET password=CONCAT(MD5('qXpassword'), ':qX') WHERE username='admin';

Delete particular order using query – Magento

SET @increment_id='43290';
SELECT @order_id:=entity_id FROM sales_order_entity WHERE increment_id=@increment_id;
DELETE FROM sales_order_entity WHERE entity_id=@order_id OR parent_id=@order_id;
DELETE FROM sales_order WHERE increment_id=@increment_id;

Mass Exclude/Unexclude Images using query – Magento

//Mass Unexclude
UPDATE`catalog_product_entity_media_gallery_value` SET `disabled` = '0' WHERE `disabled` = '1';
//Mass Exclude
UPDATE`catalog_product_entity_media_gallery_value` SET `disabled` = '1' WHERE `disabled` = '0';

Delete all products using query – Magento

TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;