How to detect and fix duplicate SKU inconsistencies between WooCommerce and your ERP without losing orders

By Kevin Marcondes · Published on Oct 28, 2026 · 8 min read

Duplicate SKUs between WooCommerce and an ERP cause lost orders, wrong items shipped, and stock mismatches. This guide shows how to detect and fix duplicate SKU inconsistencies between WooCommerce and your ERP without losing orders: SQL diagnostics, decision criteria, adjustment scripts, and a safe plan that keeps operations running.

Signs of duplicate SKUs and real risks

SKU duplication almost always appears at critical moments: checkout, marketplace integrations, or a campaign cutover. Early detection reduces impact on orders and inventory.

Practical indicators that point to SKU conflicts and why they happen.

• Orders invoiced with an item different from the cart (order shows SKU A, but SKU B was picked).
• Negative stock or overselling after ERP batch integrations.
• Orders rejected by the ERP as “product not found” even though the SKU is visible in the store.
• Sales reports that incorrectly consolidate by SKU (merging variations or distinct lines).
• Webhooks/ETL randomly failing to create/update items due to key collisions.

Common technical causes and who owns the SKU

Before fixing anything, define the source of truth for SKUs. Without that, the problem will likely return. Also check how SKUs are normalized (case, spaces, hyphens).

Typical scenarios:

• ERP exports the catalog and contains legacy SKUs that collide with new ones.
• WooCommerce allows variations to be registered with the same SKU as the parent product.
• CSV/ETL imports without uniqueness validation (case-insensitive).
• Plugins that generate SKUs automatically without checking for conflicts.
• Normalization differences: "abc-001", "ABC-001" and "abc‑001 " being treated as distinct.

Data-driven diagnosis: SQL and cross-checks

Map duplicates in the WooCommerce database considering normalization (LOWER + TRIM) and post status. Then cross-check results with ERP reports to confirm which SKUs represent distinct items.

Run these queries on a copy or staging environment. Make a backup before running any adjustments.

-- 1) Encontrar SKUs duplicados (case-insensitive, ignorando espaços)
SELECT LOWER(TRIM(pm.meta_value)) AS sku_norm,
       COUNT(*) AS total
FROM wp_postmeta pm
JOIN wp_posts p ON p.ID = pm.post_id
WHERE pm.meta_key = '_sku'
  AND pm.meta_value <> ''
  AND p.post_status IN ('publish','private')
GROUP BY sku_norm
HAVING COUNT(*) > 1
ORDER BY total DESC, sku_norm;

-- 2) Listar quais itens colidem por SKU normalizado
SELECT LOWER(TRIM(a.meta_value)) AS sku_norm,
       a.post_id AS product_id,
       p.post_type,
       p.post_title
FROM wp_postmeta a
JOIN wp_posts p ON p.ID = a.post_id
WHERE a.meta_key = '_sku'
  AND LOWER(TRIM(a.meta_value)) IN (
    SELECT LOWER(TRIM(pm.meta_value))
    FROM wp_postmeta pm
    JOIN wp_posts pp ON pp.ID = pm.post_id
    WHERE pm.meta_key = '_sku' AND pm.meta_value <> ''
    GROUP BY LOWER(TRIM(pm.meta_value))
    HAVING COUNT(*) > 1
  )
ORDER BY sku_norm, product_id;

-- 3) (Opcional) Pedidos recentes impactados por SKUs duplicados
-- Observação: itens de pedido guardam meta por linha; ajuste prefixos conforme seu schema
SELECT oi.order_id,
       oim_sku.meta_value AS sku_capturado,
       oim_pid.meta_value AS product_id
FROM wp_woocommerce_order_items oi
JOIN wp_woocommerce_order_itemmeta oim_sku
  ON oim_sku.order_item_id = oi.order_item_id AND oim_sku.meta_key = '_sku'
LEFT JOIN wp_woocommerce_order_itemmeta oim_pid
  ON oim_pid.order_item_id = oi.order_item_id AND oim_pid.meta_key = '_product_id'
WHERE oim_sku.meta_value IS NOT NULL
  AND LOWER(TRIM(oim_sku.meta_value)) IN (
    SELECT LOWER(TRIM(pm.meta_value))
    FROM wp_postmeta pm
    JOIN wp_posts pp ON pp.ID = pm.post_id
    WHERE pm.meta_key = '_sku' AND pm.meta_value <> ''
    GROUP BY LOWER(TRIM(pm.meta_value))
    HAVING COUNT(*) > 1
  )
ORDER BY oi.order_id DESC
LIMIT 200;

Fixing without losing orders: a safe plan

With duplicates mapped, perform the correction in controlled windows. The goal is to preserve links from already placed orders and prevent new collisions while you adjust the catalog.

Follow this path:

• Pause integrations that create/update products (jobs, webhooks, ETL).
• Choose the canonical SKU for each collision (rule: keep the one used in recent orders).
• Rename alternative SKUs with a temporary technical suffix (e.g., -ALT or -LEGACY).
• Update mappings in the integration/ERP to the canonical SKU (and keep the alias temporarily).
• Reprocess queues only after validating orders and stock.
• Enable idempotency in order imports to avoid duplication during catch-up.

Correction scripts: renaming duplicate SKUs via WP-CLI

You can automate safe renaming of conflicting SKUs. The example below uses WP-CLI to: identify normalized collisions, pick a canonical SKU per collision, and rename the rest with a predictable suffix. Test in staging and backup first.

After running, validate with the diagnostic queries and only then re-enable integrations.

<?php
/**
 * wp eval-file fix-sku-dupes.php
 * Requer: WP-CLI + WooCommerce carregado
 */

if (!defined('WP_CLI')) { exit; }

WP_CLI::add_command('sku:dedupe', function() {
  global $wpdb;

  // 1) Mapear duplicidades normalizadas
  $dupes = $wpdb->get_results("SELECT LOWER(TRIM(pm.meta_value)) AS sku_norm
    FROM {$wpdb->postmeta} pm
    JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key = '_sku' AND pm.meta_value <> ''
      AND p.post_status IN ('publish','private')
    GROUP BY sku_norm HAVING COUNT(*) > 1");

  foreach ($dupes as $d) {
    $rows = $wpdb->get_results($wpdb->prepare("SELECT pm.post_id, pm.meta_value AS sku
      FROM {$wpdb->postmeta} pm
      JOIN {$wpdb->posts} p ON p.ID = pm.post_id
      WHERE pm.meta_key = '_sku' AND LOWER(TRIM(pm.meta_value)) = %s
      ORDER BY p.post_date ASC", $d->sku_norm));

    if (count($rows) < 2) continue;

    // 2) Definir canônico (ex.: o mais antigo)
    $canonical = array_shift($rows); // mantém SKU original
    WP_CLI::log("Canônico para {$d->sku_norm}: #{$canonical->post_id} ({$canonical->sku})");

    // 3) Renomear restantes com sufixo previsível
    foreach ($rows as $r) {
      $new = rtrim($canonical->sku) . '-ALT-' . $r->post_id;
      update_post_meta($r->post_id, '_sku', $new);
      WP_CLI::log("Renomeado #{$r->post_id} => {$new}");
    }
  }

  WP_CLI::success('Concluído. Revise amostras e reexecute o diagnóstico.');
});

// Uso: wp sku:dedupe

Prevention: validation, queues and idempotency

Fixing is only half the job. To prevent recurrence, enforce uniqueness at multiple layers: UI/validation, integration, and persistence. Apply consistent normalization (TRIM + UPPER/LOWER) and reject duplicates before saving.

You can add a simple check in WooCommerce to block collisions during product edits or bulk imports.

• Application-level validation (block duplicate SKUs on save).
• Consistent normalization (case-insensitive) on read and write.
• Idempotency using a business key when creating orders (e.g., the store order_key). See eventual delivery and idempotency between ERP and store.
• Asynchronous, reliable processing for catalog and stock; choose a queue for inventory synchronization and, if you prefer managed, evaluate SQS and RabbitMQ options.
• Sign webhooks with HMAC to detect changes and ensure integrity: see signed webhooks (HMAC).

<?php
// Plugin mu-plugins/sku-unique-guard.php
add_action('save_post_product', 'sku_unique_guard', 10, 3);
add_action('save_post_product_variation', 'sku_unique_guard', 10, 3);

function sku_unique_guard($post_id, $post, $update) {
  if (wp_is_post_revision($post_id)) return;
  $sku = get_post_meta($post_id, '_sku', true);
  if ($sku === '' || $sku === null) return;
  global $wpdb;
  $norm = strtolower(trim($sku));
  $count = (int) $wpdb->get_var($wpdb->prepare(
    "SELECT COUNT(*) FROM {$wpdb->postmeta} pm
      JOIN {$wpdb->posts} p ON p.ID = pm.post_id
     WHERE pm.meta_key = '_sku'
       AND LOWER(TRIM(pm.meta_value)) = %s
       AND pm.post_id <> %d
       AND p.post_status IN ('publish','private','draft')",
    $norm, $post_id
  ));
  if ($count > 0) {
    // Impede colisão: aplica sufixo técnico e registra flag
    $new = trim($sku) . '-DUP-' . $post_id;
    update_post_meta($post_id, '_sku', $new);
    update_post_meta($post_id, '_sku_duplicate_flag', 1);
  }
}

Frequently asked questions

Can I change SKUs on products that were already sold without breaking integrations?

Yes, if you keep a period of alias/mapping in the integration and do not change the canonical SKU used by recent orders. Rename duplicates, preserve the canonical SKU, update the ERP, and reprocess only after validating samples in staging.

Can variations share the same SKU as the parent product?

Technically yes, but it's a bad practice. Treat each variation as a unique item with its own SKU. Duplicates confuse integrations, reports, and order fulfillment. Add validation to block collisions on save.

Should SKUs be case-sensitive?

Treat SKUs as case-insensitive for catalogs and integrations and normalize them (TRIM + UPPER/LOWER). This avoids collisions like “abc‑001”, “ABC‑001” and “abc‑001 ”. Apply the rule on import, in the UI, and at persistence.

How do I avoid losing orders during the fix?

Freeze integrations, pick a canonical SKU per collision, rename alternates with a technical suffix, validate affected orders, and only then re-enable queues. Use idempotency when creating orders to prevent duplication during reprocessing.

Do I need a queue to sync catalog and inventory?

Queues help absorb spikes and guarantee event ordering. For large catalogs and multiple channels, a queue with idempotency and reprocessing support makes it easier to detect collisions and keep store and ERP consistent.

See also

• Intermittent 502 Bad Gateway in Docker Compose with Nginx & PHP‑FPM: how to diagnose and fix timeouts and connection overloads
• How to diagnose and capture packets for intermittent network issues between Docker containers on Debian
• How to Reduce Recovery Time After a Linux Server Crash with an Ansible Playbook and a Recovery Runbook
• How to reduce build time and avoid duplicate artifacts in CI for large PHP monoliths