Remove duplicated imported orders in Woocommerce

Question

I have duplicate orders in Woocommerce and I would like to delete them to only keep unique for clean bookkeeping. I am not good at SQL, I wrote this request but it lists both duplicate when there is a duplicate. which gives me 307 results. How can I write the right request to delete the duplicates and only keep unique

Accepted Answer

First if you don&#8217;t know, woocommerce Order data Is located in four (4) tables:wp_postswp_postmetawp_woocommerce_order_itemswp_woocommerce_order_itemmetaSo the following hooked function use WPDB class and methods. It will:get all duplicated orders IDs (the lowest ID) in one query (searching for duplicated order Keys which are normally unique)delete all duplicated queried orders in one query.But be sure to always make a database backup before.The code will work do the job in one shot on any front end page load (to be removed after usage):add_action( 'template_redirect', 'progressive_delete_duplicated_orders' );function progressive_delete_duplicated_orders() {    global $wpdb;    // Get duplicated orders (smaller ID)    $duplicated_orders = (array) $wpdb->get_col("        SELECT p.ID, pm.meta_value, COUNT(*) as c        FROM {$wpdb->prefix}postmeta as pm        INNER JOIN {$wpdb->prefix}posts as p ON p.ID = pm.post_id        WHERE p.post_status = 'wc-completed'        AND pm.meta_key = '_order_key'        GROUP BY pm.meta_value        HAVING c > 1    ");    if( sizeof($duplicated_orders) == 1 )        $where_clause = 'WHERE p.ID = ' . reset($duplicated_orders);    elseif( sizeof($duplicated_orders) > 1 )        $where_clause = 'WHERE p.ID IN (' . implode( ',',$duplicated_orders ) . ')';    else return; // Exit    // Delete duplicated Orders data everywhere    $wpdb->query("        DELETE p, pm, woi, woim        FROM {$wpdb->prefix}posts as p        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id        INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON p.ID = woi.order_id        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS woim ON woi.order_item_id = woim.order_item_id        $where_clause    ");}Code goes in function.php file of your active child theme (or active theme). Tested and work.  The duplicated orders will be removed on first frontend page load. So after that you can remove or comment the code.

Advertisement

Answer