0

Need an SQL query that I can write in PHPmyAdmin to remove all spaces in SKU for all products that have attribute set seeds (which has id 10).

For example

AAA 2345 EE

needs to be

AAA2345EE
1

I was unclear on whether you mean you're changing the skus for products that have values set for a "seeds" attribute or for products that use an attribute-set called "seeds", so I am going to give both a try, and please let me know which one is correct.

Case 1 | values set for a "seeds" attribute:

The attribute-type is important for the "seeds" attribute, because it determines the table that is going to be used in the update statement's join. You can find this out by running:

select concat('catalog_product_entity_', backend_type) 
from eav_attribute where attribute_code = 'seeds';

For this examples sake, lets say it is catalog_product_entity_varchar, in which case, trying something like this should work:

update catalog_product_entity cpe
join catalog_product_entity_varchar cpev
on cpe.entity_id = cpev.entiy_id 
set cpe.sku = replace(cpe.sku, ' ', '')
where cpev.attribute_id = 10
and cpev.value is not null;

If you want to do a "dry-run" to see which values will be changed, you can run this:

select cpe.sku 'Old Sku', replace(cpe.sku, ' ', '') 'New Sku'
from catalog_product_entity cpe
join catalog_product_entity_varchar cpev
on cpe.entity_id = cpev.entiy_id 
where cpev.attribute_id = 10
and cpev.value is not null;

Case 2 | an attribute-set called "seeds":

This one is considerably easier because you already know the attribute_set_id is 10:

update catalog_product_entity
set sku = replace(sku, ' ', '')
where attribute_set_id = 10;

If you want to do a "dry-run" to see which values will be changed, you can run this:

select sku 'Old Sku', replace(sku, ' ', '') 'New Sku'
from catalog_product_entity 
where attribute_set_id = 10;

Hope this helps!

| improve this answer | |
  • Last one worked! The dry run didn't work, said it couldnt find cpe.sku, so I just did the run – Sebastiaan Aug 28 '17 at 7:18
  • @Sebastiaan pointed out that there were issues with the alias in the last "dry-run," and I realized no alias was needed. The answer has been edited to correct it. – mtr.web Aug 28 '17 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.