Woocommerce - purge old products

-------------------------
----- create backup tables for product data
----------------------


CREATE TABLE `wp_posts_20211123` ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
select * from wp_posts where post_type like 'product';

create table wp_postmeta_20211123 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
select pm.* from wp_postmeta pm join wp_posts p on p.id=pm.post_id and p.post_type like 'product';

create table wp_term_relationships_20211123 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
select * from wp_term_relationships;


-------------------------

create table posts_delete (id bigint primary key);
insert into posts_delete select p.id from wp_postmeta pm
join wp_posts p on p.id=pm.post_id and pm.meta_key like '_stock_status'
where pm.meta_value like 'outofstock' AND p.post_modified < '2021-01-01'  and p.post_type like 'product'
order by p.post_modified desc

---
--- danger danger
---

start transaction;

delete pm from wp_postmeta AS pm
join posts_delete pd on pd.id = pm.post_id;

delete p from wp_posts AS p
join posts_delete pd on pd.id = p.id;

delete r from wp_term_relationships AS r
join posts_delete pd on pd.id = r.object_id;

commit


--- count

select count(*) from wp_postmeta AS pm
join posts_delete pd on pd.id = pm.post_id;

select count(*) from wp_posts AS p
join posts_delete pd on pd.id = p.id

select count(*) from wp_term_relationships as r
join posts_delete pd on pd.id = r.object_id