Every powdercoat aluminium product had incorrect SKUs. WooCommerce was storing each powdercoat colour as a separate SKU (e.g. S/U12/S EBK, S/U12/S EBZ) while Accredo (the ERP) only tracks by base material (S/U12/S ALI). This meant SKU-based price imports always failed, and the external IT contractor could not find the root cause.
I identified the mismatch independently during a product audit, diagnosed it through phpMyAdmin, and wrote a targeted SQL query to surface all duplicate and conflicting SKUs across the full powdercoat range: 5,000+ affected variations.
The fix: I generated a WooCommerce-compatible CSV to rename all powdercoat variation SKUs to their ALI equivalent so they match Accredo exactly, while keeping the colour attribute on each variation so customers can still select their finish on the front end and it appears correctly on the order. A CSV is the standard WooCommerce bulk import format: each row maps a product ID to its corrected SKU and updated price, and the whole file uploads in one go via Products > Import. I then built a Python sync script so future Accredo price exports map automatically to a fresh CSV ready to import, with no manual work required.
5,000+ SKUs corrected
Pricing accuracy restored
Resolved where IT contractor could not
Automated sync prevents recurrence
SELECT post_id, meta_value as sku,
COUNT(*) as occurrences
FROM wp_postmeta
WHERE meta_key = '_sku'
GROUP BY meta_value
HAVING COUNT(*) > 1;
SELECT COUNT(*) as remaining_dupes
FROM wp_postmeta
WHERE meta_key = '_sku'
GROUP BY meta_value
HAVING COUNT(*) > 1;
