TimBHowe · January 12, 2022 16:49 · Jan 12, 2022
diff --git a/ClearWooData.sql b/ClearWooData.sql
@@ -0,0 +1,56 @@
+#--- Remove all users, except Admins and Shop_Managers ---
+#--Create a temp table based on the one we want to clear.
+CREATE OR REPLACE TABLE `temp_usermeta` LIKE `wp_usermeta`;
+#--Insert the data we want to keep.
+INSERT INTO `temp_usermeta` SELECT * FROM `wp_usermeta` WHERE user_id IN ( SELECT user_id FROM wp_usermeta WHERE meta_key = 'wp_capabilities' AND meta_value LIKE '%shop_manager%' OR meta_value LIKE '%administrator%' );
+#--Drop the table.
+DROP TABLE `wp_usermeta`;
+#--Rename to temp table to replace the dropped table.
+RENAME TABLE `temp_usermeta` TO `wp_usermeta`;
+
+#--Create a temp table based on the one we want to clear.
+CREATE OR REPLACE TABLE `temp_users` LIKE `wp_users`;
+#--Insert the data we want to keep.
+INSERT INTO `temp_users` SELECT * FROM `wp_users` WHERE ID IN ( SELECT user_id FROM wp_usermeta WHERE 1 );
+#--Drop the table.
+DROP TABLE `wp_users`;
+#--Rename to temp table to replace the dropped table.
+RENAME TABLE `temp_users` TO `wp_users`;
+
+#--- Remove all Order Notes ---
+#-- Create a temp table based on the one we want to clear.
+CREATE OR REPLACE TABLE `temp_comments` LIKE `wp_comments`;
+#--Insert the data we want to keep.
+INSERT INTO `temp_comments` SELECT * FROM `wp_comments` WHERE comment_type = 'order_note';
+#--Drop the table.
+DROP TABLE `wp_comments`;
+#--Rename to temp table to replace the dropped table.
+RENAME TABLE `temp_comments` TO `wp_comments`;
+
+#--Create a temp table based on the one we want to clear.
+CREATE OR REPLACE TABLE `temp_commentmeta` LIKE `wp_commentmeta`;
+#--Insert the data we want to keep.
+INSERT INTO `temp_commentmeta` SELECT * FROM `wp_commentmeta` WHERE comment_id IN ( SELECT comment_ID FROM wp_comments WHERE 1 );
+#--Drop the table.
+DROP TABLE `wp_commentmeta`;
+#--Rename to temp table to replace the dropped table.
+RENAME TABLE `temp_commentmeta` TO `wp_commentmeta`;
+
+#--- Remove all Shop_Orders and Shop_Subscriptions ---
+#-- Create a temp table based on the one we want to clear.
+CREATE OR REPLACE TABLE `temp_posts` LIKE `wp_posts`;
+#--Insert the data we want to keep.
+INSERT INTO `temp_posts` SELECT * FROM `wp_posts` WHERE post_type <> 'shop_order' AND post_type <> 'shop_subscription';
+#--Drop the table.
+DROP TABLE `wp_posts`;
+#--Rename to temp table to replace the dropped table.
+RENAME TABLE `temp_posts` TO `wp_posts`;
+
+#--Create a temp table based on the one we want to clear.
+CREATE OR REPLACE TABLE `temp_postmeta` LIKE `wp_postmeta`;
+#--Insert the data we want to keep.
+INSERT INTO `temp_postmeta` SELECT * FROM `wp_postmeta` WHERE post_id IN ( SELECT ID FROM wp_posts WHERE 1 );
+#--Drop the table.
+DROP TABLE `wp_postmeta`;
+#--Rename to temp table to replace the dropped table.
+RENAME TABLE `temp_postmeta` TO `wp_postmeta`;