WooCommerce : Delete all failed orders

woocommerce-delete-failed-orders.sql

-- Below queries are just to check the failed order records:

SELECT * FROM `wp_woocommerce_order_itemmeta` WHERE order_item_id IN (SELECT order_item_id FROM `wp_woocommerce_order_items` WHERE order_id IN (SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed'))

SELECT * FROM `wp_woocommerce_order_items` WHERE order_id IN (SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed')

SELECT * FROM wp_comments WHERE comment_type = 'order_note' AND comment_post_ID IN(SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed')

SELECT * FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed')

SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed'


-- Below are the actual queries to delete all failed orders. Make sure to run them in same order otherwise it will not work properly.

DELETE FROM `wp_woocommerce_order_itemmeta` WHERE order_item_id IN (SELECT order_item_id FROM `wp_woocommerce_order_items` WHERE order_id IN (SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed'))

DELETE FROM `wp_woocommerce_order_items` WHERE order_id IN (SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed')

DELETE FROM wp_comments WHERE comment_type = 'order_note' AND comment_post_ID IN(SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed')

DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed')

DELETE FROM wp_posts WHERE post_type = 'shop_order' AND post_status='wc-failed'