Magento - SQL - Select all products by attribute and update it

Magento - SQL - Select all products by attribute and update it

September 24, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento - SQL - Select all products by attribute and update it

Updating Product Attributes in Magento Using SQL

As a senior developer working on e-commerce platforms, I frequently encounter requests like yours where we need to select products based on specific attributes and then perform bulk updates. In this post, I’ll guide you through the process of selecting products by an attribute (in this case, barcolor) and updating that attribute value in Magento using SQL.

Understanding the Requirements

You want to:

  1. Select all products whose barcolor attribute is currently set to “LIGHT GREEN”.
  2. Update these selected products’ barcolor attribute to “GREEN”.

Accessing the Database Directly

Magento uses MySQL as its database management system. Before we proceed, ensure you have proper access rights and that your Magento installation allows direct SQL queries.

Step 1: Select Products by Attribute Value

We’ll start by selecting all products with the barcolor attribute set to “LIGHT GREEN”. Here’s the SQL query for this:

SELECT * FROM catalog_product_entity AS pce
INNER JOIN catalog_product_entity_varchar AS pcve ON (pce.entity_id = pcve.entity_id)
WHERE pcve.attribute_id = (
    SELECT e.attribute_id
    FROM eav_attribute AS e
    WHERE e.attribute_code = 'barcolor'
) AND pcve.value = 'LIGHT GREEN';

Replace catalog_product_entity and catalog_product_entity_varchar with your actual table names if they have been modified.

Step 2: Update Products’ Attribute Value

Next, we’ll update the selected products’ barcolor attribute to “GREEN”. Here’s how you can do this:

UPDATE catalog_product_entity AS pce
INNER JOIN catalog_product_entity_varchar AS pcve ON (pce.entity_id = pcve.entity_id)
SET pcve.value = 'GREEN'
WHERE pcve.attribute_id = (
    SELECT e.attribute_id
    FROM eav_attribute AS e
    WHERE e.attribute_code = 'barcolor'
) AND pcve.value = 'LIGHT GREEN';

Again, replace the table names if necessary.

Best Practices and Considerations

  • Always backup your database before performing bulk updates.
  • Be careful with UPDATE statements to avoid updating more records than intended.
  • If possible, consider using Magento’s built-in API or tools like the “Import” feature for mass updates. Direct SQL queries should be used only when necessary.

Conclusion

Selecting and updating product attributes in Magento at a database level is not recommended by the official documentation due to potential risks as explained on this page from our blog. However, for experienced developers working under proper testing environments, these methods can provide quick solutions.

Remember that all changes should be thoroughly tested and backed up to prevent data loss. It’s always best to use Magento’s built-in features where possible.

If you have any other questions about Magento development or need further assistance with your e-commerce platform, feel free to reach out to our team at Laravel Company. We provide a range of services including custom development, migrations, and support.

Catalog Database

Our network