Useful direct SQL queries for Magento 2 developers

This post covers essential queries to efficiently manage products, customers, orders, and more—helping developers troubleshoot issues, extract insights, and optimize database operations. Whether you’re debugging or streamlining workflows, these queries will save you time and effort.

Select product default Attribute set id

-- Entity type id = 4 -> The product entity
SELECT attribute_set_id 
FROM `eav_attribute_set` 
WHERE entity_type_id = 4 
AND attribute_set_name = 'Default';

Select product Attribute Group Id by using attribute_group_code and Attribute Set ID

SELECT attribute_group_id 
FROM `eav_attribute_group` 
WHERE attribute_group_code = 'iyngaran' 
AND attribute_set_id = '4';

Update attribute_group_id for product attribute

-- attribute_set_id = '4' // the default attribute set 
-- attribute_code = 'iyngaran_pro_val'
-- attribute_group_code = 'iyngaran'
UPDATE `eav_entity_attribute` 
SET `attribute_group_id` = (
    SELECT attribute_group_id 
    FROM `eav_attribute_group` 
    WHERE attribute_group_code = 'iyngaran' 
    AND attribute_set_id = '4'
) 
WHERE `eav_entity_attribute`.`attribute_id` = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code = 'iyngaran_pro_val'
);

Update sort_order for product attribute

-- sort_order = 2
-- attribute_code = 'iyngaran_pro_val'
-- attribute_group_code = 'iyngaran'
UPDATE `eav_entity_attribute` 
SET `sort_order` = 2 
WHERE `attribute_id` = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code = 'iyngaran_pro_val'
) 
AND `attribute_group_id` = (
    SELECT attribute_group_id 
    FROM `eav_attribute_group`
    WHERE attribute_group_code = 'iyngaran' 
    AND attribute_set_id = '4'
);

Delete a custom attribute using entity_type_id and attribute_code

For this example, I am going to delete a product attribute, which has attribute_code as ‘iyn_product_option’. To delete a custom attribute, we need to delete the data from the following tables.

Tables: eav_attribute_option_value, eav_attribute_option_swatch, eav_attribute_label, eav_attribute_option, and eav_attribute.

-- First find out the entity_type_id for the custom attribute.
SELECT entity_type_id 
FROM `eav_entity_type` 
WHERE `entity_type_code` = 'catalog_product';

-- Delete attribute option values using entity_type_id and attribute_code.
DELETE FROM eav_attribute_option_value 
WHERE option_id IN (
    SELECT option_id 
    FROM eav_attribute_option 
    WHERE attribute_id = (
        SELECT attribute_id 
        FROM eav_attribute 
        WHERE attribute_code = 'iyn_product_option' 
        AND entity_type_id = 4
    )
);

-- Delete attribute option swatches using entity_type_id and attribute_code.
DELETE FROM eav_attribute_option_swatch 
WHERE option_id IN (
    SELECT option_id 
    FROM eav_attribute_option 
    WHERE attribute_id = (
        SELECT attribute_id 
        FROM eav_attribute 
        WHERE attribute_code = 'iyn_product_option' 
        AND entity_type_id = 4
    )
);

-- Delete attribute label using entity_type_id and attribute_code.
DELETE FROM eav_attribute_label 
WHERE attribute_id IN (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code = 'iyn_product_option' 
    AND entity_type_id = 4
);

-- Delete attribute options using entity_type_id and attribute_code.
DELETE FROM eav_attribute_option 
WHERE attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code = 'iyn_product_option' 
    AND entity_type_id = 4
);

-- Finally, delete the attribute using entity_type_id and attribute_code.
DELETE FROM eav_attribute 
WHERE attribute_code = 'iyn_product_option' 
AND entity_type_id = 4;

Delete all products

SET FOREIGN_KEY_CHECKS = 0;

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`;
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_link_type`;
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_label`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;

TRUNCATE TABLE `catalog_product_entity`;

INSERT INTO `catalog_product_link_type`(`link_type_id`, `code`) 
VALUES 
    (1, 'relation'),
    (2, 'bundle'),
    (3, 'super'),
    (4, 'up_sell'),
    (5, 'cross_sell');

INSERT INTO `catalog_product_link_attribute
(`product_link_attribute_id`, `link_type_id`, `product_link_attribute_code`, `data_type`) 
VALUES 
    (1, 2, 'qty', 'decimal'),
    (2, 1, 'position', 'int'),
    (3, 4, 'position', 'int'),
    (4, 5, 'position', 'int'),
    (6, 1, 'qty', 'decimal'),
    (7, 3, 'position', 'int'),
    (8, 3, 'qty', 'decimal');

INSERT INTO `cataloginventory_stock`(`stock_id`, `website_id`, `stock_name`) 
VALUES 
    (1, 0, 'Default');

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM url_rewrite 
WHERE entity_type='product';

To remove the URL rewrites that were automatically generated, you can use this one.

DELETE FROM url_rewrite 
WHERE entity_type='product' 
AND is_autogenerated='1';

Select all the disabled products in Magento2

SELECT entity_id 
FROM `catalog_product_entity_int`
WHERE attribute_id = (
    SELECT attribute_id 
    FROM `eav_attribute`
    WHERE `attribute_code` LIKE 'status'
) 
AND `catalog_product_entity_int`.value = 2;

OR

SELECT
    `eav_attribute`.`attribute_id` AS `attribute_id`,
    `catalog_product_entity_int`.`entity_id` AS `entity_id`,
    `catalog_product_entity_int`.`value` AS `value`,
    `eav_attribute`.`attribute_code` AS `attribute_code`,
    `catalog_product_entity`.`sku` AS `sku`,
    `catalog_product_entity`.`created_at` AS `created_at`,
    `catalog_product_entity`.`updated_at` AS `updated_at`
FROM
    ((`eav_attribute`
    JOIN `catalog_product_entity_int` ON 
    (`eav_attribute`.`attribute_id` = `catalog_product_entity_int`.`attribute_id`))
    JOIN `catalog_product_entity` ON
    (`catalog_product_entity_int`.`entity_id` = `catalog_product_entity`.`entity_id`))
WHERE
    (`eav_attribute`.`attribute_code` = 'status') 
    AND (`catalog_product_entity_int`.`value` = 2);