WooCommerce SQL Procedures

woocommerce-procedures.sql

-- https://www.w3schools.com/sql/sql_stored_procedures.asp
-- https://codex.wordpress.org/Database_Description
-- https://github.com/woocommerce/woocommerce/wiki/Database-Description

CREATE PROCEDURE DeleteProduct @ProductID bigint(20)
AS
    DELETE FROM wp_posts WHERE ID = @ProductID;
    DELETE FROM wp_postmeta WHERE post_id = @ProductID;

    DELETE c, cm 
        FROM wp_comments AS c 
        LEFT JOIN wp_commentmeta AS cm
        ON c.comment_ID = cm.comment_id
        WHERE c.comment_post_ID = @ProductID;

    DELETE FROM wp_term_relationships WHERE object_id = @ProductID;

    -- WooCommerce
    DELETE FROM wp_wc_product_meta_lookup WHERE product_id = @ProductID;
    DELETE FROM wp_wc_order_product_lookup WHERE product_id = @ProductID;
    DELETE FROM wp_woocommerce_downloadable_product_permissions WHERE product_id = @ProductID;
    DELETE FROM wp_woocommerce_order_itemmeta WHERE meta_key = '_product_id' AND meta_value = @ProductID;

    -- WP ALL IMPORT & EXPORT
    DELETE FROM wp_pmxi_posts WHERE post_id = @ProductID;
    DELETE FROM wp_pmxe_posts WHERE post_id = @ProductID;

    -- Mollie
    DELETE FROM wp_mollie_pending_payment WHERE post_id = @ProductID;

    -- Yith Wishlist
    DELETE FROM wp_yith_wcwl WHERE prod_id = @ProductID;
GO;
-- EXEC DeleteProduct @ProductID = 1234;



CREATE PROCEDURE MergeProduct @OldProductID bigint(20), @NewProductID bigint(20)
AS
    DELETE FROM wp_posts WHERE ID = @OldProductID;
    DELETE FROM wp_postmeta WHERE post_id = @OldProductID;

    DELETE c, cm 
        FROM wp_comments AS c 
        LEFT JOIN wp_commentmeta AS cm
        ON c.comment_ID = cm.comment_id
        WHERE c.comment_post_ID = @OldProductID;

    DELETE FROM wp_term_relationships WHERE object_id = @OldProductID;

    -- WooCommerce
    DELETE FROM wp_wc_product_meta_lookup WHERE product_id = @OldProductID;
    DELETE FROM wp_wc_order_product_lookup WHERE product_id = @OldProductID;
    UPDATE wp_wc_order_product_lookup SET product_id = @NewProductID WHERE product_id = @OldProductID;
    UPDATE wp_woocommerce_order_itemmeta SET meta_value = @NewProductID WHERE meta_key = '_product_id' AND meta_value = @OldProductID;

    -- WP ALL IMPORT & EXPORT
    DELETE FROM wp_pmxi_posts WHERE post_id = @ProductID;
    DELETE FROM wp_pmxe_posts WHERE post_id = @ProductID;

    -- Mollie
    DELETE FROM wp_mollie_pending_payment WHERE post_id = @ProductID;

    -- Yith Wishlist
    UPDATE wp_yith_wcwl SET prod_id = @NewProductID WHERE prod_id = @OldProductID;
GO;
-- EXEC MergeProduct @OldProductID = 1234, @NewProductID = 1234;