Updating Attributes for Product Variants

Overview

You may need to change attributes (e.g., size, color, etc.) for existing product variants when attributes were configured incorrectly.

Solution

As this change cannot be done from UI, submit a request to Symphony Support, including the following information:

• Product Variant ID
• Attribute type (e.g., size) and value that need to be assigned to the product variant

<supportagent>

1. Connect to the database and search for the existing attributes with the values provided by the brand:
   

   select * from Attributes where value in ('<value 1>','<value 2>') order by value;

   For example:
   
   
   
   
2. Search for current attribute IDs assigned to the variants:
   

   select * from Attributes 
   where id in (select distinct Attributes_id from Product_Attributes where Product_id 
   in (<Product Variant IDs separated by comma>)) 
   and attributeKey_id=<AttributeKey_id from step 1>

   Sample output:
   

   # id, attributeKey_id, value, displayType, displayValue, priority, isDefaultable
   23812, 3400, 323d, SIZE, 323D, 0, 0
   23798, 3400, 343d, SIZE, 343D, 0, 0

3. Find which Attribute IDs are currently linked to the Product Variant IDs:
   

   select * from Product_Attributes 
   where Attributes_id 
   in (select id from Attributes where id in 
   (select distinct Attributes_id from Product_Attributes 
   where Product_id in (<Product Variant IDs separated by comma>)) 
   and attributeKey_id=<AttributeKey_id from step 1>) 
   and Product_id in (<Product Variant IDs separated by comma>);

   Sample output:
   

   # Product_id, Attributes_id
   889168, 23812
   889169, 23798

4. Create a Change Request Jira ticket under the SPC project, including the following queries for each product variant:
   
   • Update query:

     update Product_Attributes set Attributes_id=<required attribute ID from step 1>
     where Product_id=<product variant ID> and Attributes_id=<old attribute ID from step 3>;

     For example, if we need to assign 32ddd value to the product variant ID 889168:
     

     update Product_Attributes set Attributes_id=31118 
     where Product_id=889168 and Attributes_id=23812;

     
     Note: If you need to remove a specific color/ swatch from a product, use the following query instead:
     
     

     Delete from Product_Attributes 
     WHERE Product_id=<product variant ID> 
     and Attributes_id=<required attribute ID from step 1>;

     
     Rollback query:

   • update Product_Attributes set Attributes_id=<initial/old attribute ID>
     where Product_id=<product variant ID> and Attributes_id=<new attribute ID>
     

5. Once the Jira ticket is complete, run the query from step 3 to confirm the change.

</supportagent>