Deleting attribute values associated with rule-based sales categories

You can delete or remove attribute values used as filters in an RBSC without impacting the RBSC itself.

About this task

When attribute values are associated with an RBSC as a filter, the RBSC may disappear from the front end if the attribute value is deleted from the system. To prevent this, follow these steps:

Procedure

  1. Identify the attribute values that need to be deleted.
  2. Retrieve the corresponding ATTRVAL_IDs from the ATTRVAL table.
  3. Verify whether these attribute values are used in any RBSC by running the following query. 
    SELECT * FROM DMELEMENTNVP
WHERE DMELEMENTNVP.DMELEMENT_ID IN (
SELECT DMELEMENT.DMELEMENT_ID
FROM DMELEMENT
JOIN DMACTIVITY ON DMELEMENT.DMACTIVITY_ID = DMACTIVITY.DMACTIVITY_ID
WHERE DMACTIVITY.NAME LIKE 'DynamicSalesCategoryActivity%'
AND DMELEMENTNVP.NAME= 'filterValue'
AND DMELEMENTNVP.VALUE = '7000000000000010577'
);

    Where DMELEMENTNVP.VALUE is ATTRVAL_ID.

  4. After confirming the results above, delete the following rows: 
    DELETE FROM DMELEMENTNVP
WHERE DMELEMENTNVP.DMELEMENT_ID IN (
SELECT DMELEMENT.DMELEMENT_ID
FROM DMELEMENT
JOIN DMACTIVITY ON DMELEMENT.DMACTIVITY_ID = DMACTIVITY.DMACTIVITY_ID
WHERE DMACTIVITY.NAME LIKE 'DynamicSalesCategoryActivity%'
AND DMELEMENTNVP.NAME= 'filterValue'
AND DMELEMENTNVP.VALUE = '7000000000000010577'
);

    Where DMELEMENTNVP.VALUE is ATTRVAL_ID.

    Note: Remove the reference to ATTRVAL_ID in the DMELEMENTNVP.VALUE table before deleting the corresponding attribute value from the ATTRVAL table.
  5. Delete the attribute values from the ATTRVAL table using a data load process to ensure accuracy and efficiency.
    1. Prepare a data load file containing the ATTRVAL_IDs to be deleted.
    2. Use the appropriate data load utility or script to remove the attribute values from the system.
    3. Validate the deletion by checking if the ATTRVAL_IDs no longer exist in the ATTRVAL table.
    Note: Ensure that the attribute values are not referenced in other areas before deletion to avoid system inconsistencies.