WooCommerce SQL Snippet

Delete WooCommerce Products by Import ID with SQL

Learn how to bulk delete WooCommerce products imported through WP All Import, using a targeted SQL query based on the import_id.

WooCommerce WP All Import SQL Cleanup
Delete WooCommerce products by Import ID with SQL

What this SQL query does

Managing thousands of WooCommerce products often involves bulk imports with WP All Import. When you need to remove all products created by a specific import, you can target the related import_id directly in the database.

The query collects the products and variations from the selected import, then removes their related database records, including post meta, term relationships, lookup records, and optionally linked attachments.

Targeted deletion Deletes only products connected to a specific WP All Import batch.
Clean database cleanup Removes products, variations, postmeta, term relationships and lookup records.
Useful for feeds Ideal when you need to fully replace a supplier product feed.

1) Why delete products by Import ID?

Deleting products by import_id is often safer and faster than manually removing products one by one.

  • You ran a test import on staging and need to undo it.
  • You accidentally imported duplicate WooCommerce products.
  • You want to replace an entire supplier product feed.
  • You need to remove only the products from one specific import without affecting the rest of the catalogue.
WP All Import keeps a connection between imported posts and the import that created them through the wp_pmxi_posts table.

2) Before running the query

Because this query performs a destructive database action, you should always validate everything first.

  • Create a full database backup.
  • Test the query on a staging environment first.
  • Confirm the correct @IMPORT_ID.
  • Replace the prefix A9B2c1u_ with your real WordPress database table prefix.
  • Do not run this on production unless you are completely sure about the result.
For extra safety, run the preview SELECT query first so you can review the product IDs before executing any DELETE query.

3) SQL query to delete products from a specific import

Replace @IMPORT_ID with the ID of the import you want to clean up and replace A9B2c1u_ with your actual WordPress database table prefix.

woocommerce-delete-products-by-import-id.sql 
-- 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;
The query uses temporary tables to keep a stable list of IDs during execution.

4) How this query works

It defines the Import ID With SET @IMPORT_ID = 1;, you define which WP All Import batch you want to clean up.
It collects products and variations The query finds all posts of type product and product_variation connected to that import.
It deletes related attachments The optional attachment step removes media items that have one of the target products as their parent.
It removes related records The cleanup removes records from postmeta, term_relationships and wc_product_meta_lookup.
It deletes the posts Finally, the products and variations are deleted from the posts table.

5) How to find the correct Import ID

The import_id is the ID of the import created by WP All Import. You can find it from the WordPress admin or directly in the database.

  • Go to WP All Import → Manage Imports and check the import ID.
  • Alternatively, check the wp_pmxi_imports table in the database.
  • Before deleting anything, run a SELECT query against wp_pmxi_posts to confirm that the import contains the correct products.
preview-products-before-delete.sql 
SET @IMPORT_ID = 1;

SELECT p.ID, p.post_title, p.post_type, p.post_status
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')
ORDER BY p.ID DESC;

6) Where to run the query

You can execute this query in different ways depending on your level of database access.

  • phpMyAdmin: from the SQL tab of your database.
  • Adminer: if you use a lightweight database management tool.
  • WP-CLI: with wp db query, if you have SSH access.
  • MySQL client: directly from the terminal for more advanced control.
On large stores, test on staging first and run the cleanup during low-traffic hours.

7) Best practices

  • Always test the query on staging before running it on production.
  • Double-check the value of @IMPORT_ID.
  • Create a full database backup before execution.
  • Run the SELECT preview query first to see which products will be deleted.
  • Make sure the database table prefix is correct in every table name.
  • After execution, clear cache and review the WooCommerce product list.

8) Frequently Asked Questions

Will this delete all WooCommerce products?

No. The query targets only products and variations linked to the selected import_id.

Do I need to change the table prefix?

Yes. This example uses A9B2c1u_. If your prefix is wp_, you must replace it in every table name.

Are product variations deleted too?

Yes. The query targets both product and product_variation post types.

Are product images deleted too?

The query includes an optional step for attachments that have one of the deleted products as their post_parent.

Can I undo this after running it?

Not easily. That is why you should always create a full database backup before executing the query.

9) Support

For any issue or question related to WooCommerce SQL snippets, you can contact technical support.

info@vgdevsolutions.gr

Related posts