vgdevsolutions
0

My Cart

0

My Cart

Portfolio

Delete WooCommerce Products from a Specific WP All Import in SQL

Managing thousands of products in WooCommerce often requires bulk imports with WP All Import. But what if you need to remove all products that came from a specific import?

Instead of manually deleting them one by one, you can run an SQL query directly in your database. This method ensures a clean deletion of products, variations, and their related data (attachments, metadata, relationships).

Why Delete Products by Import ID?

There are several cases where this becomes necessary:

  • You tested an import on staging and want to roll it back.

  • You accidentally imported duplicate products.

  • You want to replace an entire product feed from a supplier.

By targeting the import_id used by WP All Import, you can safely remove everything linked to that batch.

SQL Query to Delete Products from a Specific Import

⚠️ Important: Always create a database backup before running destructive queries.
Replace A9B2c1u_ with your own WordPress table prefix, and set your @IMPORT_ID.

🗃️
woocommerce-delete-products-by-import-id.sql
Copy to clipboard
-- Set your specific import ID here
SET @IMPORT_ID = 1;

-- 1) Collect ALL products & variations from this import
CREATE TEMPORARY TABLE to_del (id BIGINT PRIMARY KEY);

INSERT INTO to_del
SELECT p.ID
FROM A9B2c1u_posts p
JOIN A9B2c1u_pmxi_posts x ON x.post_id = p.ID
WHERE x.import_id = @IMPORT_ID
  AND p.post_type IN ('product','product_variation');

-- 2) (Optional but recommended) Delete related attachments
CREATE TEMPORARY TABLE to_del_att (id BIGINT PRIMARY KEY);

INSERT INTO to_del_att
SELECT a.ID
FROM A9B2c1u_posts a
WHERE a.post_type = 'attachment'
  AND a.post_parent IN (SELECT id FROM to_del);

DELETE pm FROM A9B2c1u_postmeta pm
JOIN to_del_att da ON pm.post_id = da.id;

DELETE tr FROM A9B2c1u_term_relationships tr
JOIN to_del_att da ON tr.object_id = da.id;

DELETE a FROM A9B2c1u_posts a
JOIN to_del_att da ON a.ID = da.id;

DROP TABLE to_del_att;

-- 3) Delete products/variations + all related records
DELETE pm FROM A9B2c1u_postmeta pm
JOIN to_del d ON pm.post_id = d.id;

DELETE tr FROM A9B2c1u_term_relationships tr
JOIN to_del d ON tr.object_id = d.id;

DELETE wcl FROM A9B2c1u_wc_product_meta_lookup wcl
JOIN to_del d ON wcl.product_id = d.id;

DELETE p FROM A9B2c1u_posts p
JOIN to_del d ON p.ID = d.id;

DELETE x FROM A9B2c1u_pmxi_posts x
JOIN to_del d ON x.post_id = d.id;

DROP TABLE to_del;

How This Query Works

  1. Collects all products and variations imported with a specific import_id.

  2. Deletes their attachments (images, files) to keep your database clean.

  3. Removes all related data including:

    • Postmeta

    • Term relationships (categories, tags)

    • Product lookup table entries

    • WP All Import references

  4. Finally, it deletes the actual posts (product and product_variation).

Best Practices

  • ✅ Always test on staging before running in production.

  • ✅ Double-check the @IMPORT_ID value.

  • ✅ Take a full database backup before execution.

  • ✅ Run queries via phpMyAdmin or WP-CLI wp db query.