SQL script to delete LearnPress processing orders from the database

gistfile1.txt

-- First, create a backup of the orders and their meta (HIGHLY RECOMMENDED)
CREATE TABLE wp_posts_backup_processing_orders AS 
SELECT * FROM wp_posts 
WHERE post_type = 'lp_order' 
AND post_status = 'lp-processing';

CREATE TABLE wp_postmeta_backup_processing_orders AS 
SELECT pm.* FROM wp_postmeta pm 
INNER JOIN wp_posts p ON p.ID = pm.post_id 
WHERE p.post_type = 'lp_order' 
AND p.post_status = 'lp-processing';

SELECT COUNT(*) 
FROM wp_posts 
WHERE post_type = 'lp_order' 
AND post_status = 'lp-processing';

DELETE pm 
FROM wp_postmeta pm 
INNER JOIN wp_posts p ON p.ID = pm.post_id 
WHERE p.post_type = 'lp_order' 
AND p.post_status = 'lp-processing';

DELETE FROM wp_posts 
WHERE post_type = 'lp_order' 
AND post_status = 'lp-processing';

SELECT COUNT(*) 
FROM wp_posts 
WHERE post_type = 'lp_order' 
AND post_status = 'lp-processing';

DROP TABLE wp_posts_backup_processing_orders;
DROP TABLE wp_postmeta_backup_processing_orders;

-- Optimize the tables after large deletion
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;

-- For InnoDB tables use this instead of the last 2 queries
ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_postmeta ENGINE=InnoDB;