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`;