Solved: SQL change all products to virtual / not virtual in WooCommerce via phpMyAdmin

Result of changing all products to not-virtual via a database SQL query
Result of changing all products to not-virtual via a database SQL query

Might you need to bulk edit products to assign them all virtual or not virtual the best way is to do it via the database.

I had for my customer over 5000 products in WooCommerce all marked “virtual” by mistake from import. Yes, I tried bulk editing tools like WP Sheet Editor and Bulk Table Editor for WooCommerce but editing 5000 products with them was going to take too much time. I needed a faster solution.

After a bit of investigation I found that virtual value is stored in WordPress tables and can be edited to have value yes or no.

The query is very simple.

Checking data before changing

Backup your database first. Very easy with free backup plugin UpdraftPlus that lets you backup-restore only database too and restore only database too if needed. Good simple solution.

You can in phpMyAdmin navigate to your postmeta table (prefix can be wp_ or whatever you have set), then you can perform a search in it by meta_key “_virtual”. This displays the results and you can see that your products have virtual values of yes and no.

SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '_virtual'

In column post_id you see post_id of WooCommerce products so you already figured out to change each product to virtual or not virtual the value of meta_key “_virtual” must be changed yes or no.

Making changes in database

Hoping you already did a backup of your database as mentioned earlier. If not, do it now. Doing these SQL-s can break things and you need a point to restore to.

Changing all products to virtual by SQL:

In the SQL section of your phpMyAdmin run this query (insert query, then copy paste this query into the SQL window)

UPDATE `wp_postmeta` SET `meta_value` = 'yes' WHERE `meta_key` LIKE '_virtual'

Changing all products to NOT virtual by SQL:

UPDATE `wp_postmeta` SET `meta_value` = 'no' WHERE `meta_key` LIKE '_virtual'

You can first click “Simulate” button to simulate the query – it will return how many matches it found. If that looks OK, click Go to apply the query to the database.

You should now see something like this returned for your query.

This worked for me. Hoping it helps someone else. Please take good care when doing database operations directly in database and always have restorable backups before making any changes like mentioned in this article in the database.

Post Author: Tom Pai