#woocommerce

purge-woocommerce-data.sql

/**
 * USE AT YOUR OWN RISK!
 *
 * In no way do I guarantee the accuracy of these queries to do exactly what you want on your own server.
 * I have written them down for my own personal record, and you are free to use at your own discretion,
 * and provide corrections.
 */

/** 
 * 1. Delete order and coupon meta data.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with regard to your server specs.
 */
DELETE FROM `wp_postmeta` WHERE `post_id` IN (
	SELECT `ID` FROM `wp_posts` WHERE `post_type` IN ( 
		'shop_order',
		'shop_order_refund',
		'shop_subscription',
		'shop_coupon',
		'scheduled-action',
		'payment_retry',
		'sub_transaction',
		'subscription'
	)
) LIMIT 100000;

/**
 * 2. Delete order notes meta.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with regard to your server specs.
 */
DELETE FROM `wp_commentmeta` WHERE `comment_id` IN (
	SELECT `comment_ID` FROM `wp_comments` WHERE `comment_post_ID` IN (
		SELECT `ID` FROM `wp_posts` WHERE `post_type` IN (
			'shop_order',
			'shop_order_refund',
			'shop_subscription',
			'scheduled-action',
			'payment_retry',
			'sub_transaction',
			'subscription'
		)
	)
) LIMIT 100000;

/**
 * 3. Delete order notes.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with regard to your server specs.
 */
DELETE FROM `wp_comments` WHERE `comment_post_ID` IN (
	SELECT `ID` FROM `wp_posts` WHERE `post_type` IN (
		'shop_order',
		'shop_order_refund',
		'shop_subscription',
		'scheduled-action',
		'payment_retry',
		'subscription',
		'sub_transaction'
	)
) LIMIT 100000;

/**
 * 4. Delete the order and comment objects.
 *
 * Note the LIMIT is for performance reasons; 
 * you may be able to increase, or you may need 
 * to decrease, with regard to your server specs.
 */
DELETE FROM `wp_posts` WHERE `post_type` IN ( 
	'shop_order', 
	'shop_order_refund', 
	'shop_subscription', 
	'shop_coupon',
	'scheduled-action',
	'payment_retry',
	'subscription',
	'sub_transaction'
) LIMIT 100000;

/**
 * 5. Truncate order items meta.
 */
TRUNCATE TABLE `wp_woocommerce_order_itemmeta`;

/**
 * 6. Truncate order items.
 */
TRUNCATE TABLE `wp_woocommerce_order_items`;

/**
 * 7. Delete user accounts for customers.
 */
DELETE FROM `wp_users` WHERE `ID` IN (
	SELECT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities' AND `meta_value` IN ( 'a:1:{s:8:"customer";b:1;}', 'a:1:{s:19:"subscribed_customer";b:1;}' )
) LIMIT 100000;

/**
 * 8.1. Delete temporary table of customer user IDs.
 */
DROP TEMPORARY TABLE IF EXISTS `_wc_cleanup__customer_ids`;

/**
 * 8.2. Create temporary table of next 25,000 user IDs with only `customer` or `subscribed_customer` role.
 */
CREATE TEMPORARY TABLE `_wc_cleanup__customer_ids`
SELECT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities' AND `meta_value` IN ( 'a:1:{s:8:"customer";b:1;}', 'a:1:{s:19:"subscribed_customer";b:1;}' ) LIMIT 25000;

/**
 * 8.3. Delete user meta of customer user IDs in temporary table.
 */
DELETE a.* FROM `wp_usermeta` a WHERE `user_id` IN (
	SELECT `user_id` FROM `_wc_cleanup__customer_ids`
);

/** Repeat steps 8.1 - 8.3 until completed. */