Bulk Edit & Create WooCommerce Coupons from Database using PhpMyadmin

The fastest & easiest way to edit WC Coupons is via database edit.

Having dozens of coupons in WooCommerce shop is very easy to manage. But when it comes to hundreds or more coupons it is going to be a big headache to manage them especially when you want to change or edit the coupon value in one go. So here is the best way so far to edit the coupon values in bulk.

Here we’re using Phpmyadmin for the database table queries. Some reference can be found in Stackoverflow answer here.

This guide was tested using Woocommerce version 8.4.

Edit WooCommerce Coupons in Database

1. First things first, Backup the database.
2. Log into Phpmyadmin and select the WP database.
3. Identify the table prefix and coupon info that need to be updates based on the table below.
4. Run the sql query.
5. Do changes on the fly

Standard WC meta_key list:

In Phpmyadmin, click the “Query” tab and fill in the following in the query box.

SELECT * FROM `gozf_postmeta` WHERE `meta_key` = 'coupon_amount'

Change “gozf_postmeta” to your table prefix. Change the meta_key to the value that need to be updated.

***Do take note on the ` and ‘ symbol. Make sure they’re correct, otherwise it will return query error.

Once the query submitted, it will returns list of coupons ids with meta_key column required. To update or change the value, we can simply Double Click on each meta_value box and then insert the new value. To apply the changes, simply click anywhere outside the box.

We can also use the Check All options to do bulk change.

For 1000’s of coupons, we can also use the sql query method, provided the meta_id is known using the following query.

UPDATE `wp543`.`gozf_postmeta` SET `meta_value` = '1' WHERE `gozf_postmeta`.`meta_id` = 348652

How to Create Dozens of Woocommerce Coupon in Bulk?

Now, another use case of using phpmyadmin to manage Woocommerce coupon is when we want to create many coupons in bulk. Instead of key in the coupon data one-by-one via the WP Admin or using special plugin that enable CSV import, we can also use database SQL query to automate this process.

In WooCommerce, coupons are stored in the WordPress database. Specifically, the coupon data is stored in two database tables: wp_posts and wp_postmeta.

1. wp_posts table: Coupons are stored as custom post types with the post_type set to ‘shop_coupon’. Each coupon is represented as a row in this table, with various columns storing information such as the coupon code, description, and other settings.
2. wp_postmeta table: The associated data for each coupon is stored in this table. Each row in this table corresponds to a specific meta data entry for a coupon. The post_id column in this table is used to link the coupon data to the corresponding row in the wp_posts table.

To retrieve all the coupons from the database, you can use the following MySQL query:

SELECT * FROM `wp_posts` WHERE `post_type` = ‘shop_coupon’ ORDER BY `ID` DESC

Additionally, if you want to retrieve specific coupon details, you can use a more complex query that joins the wp_posts and wp_postmeta tables:

SELECT p.`ID`,
p.`post_title` AS coupon_code,
p.`post_excerpt` AS coupon_description,
— other coupon fields
FROM `wp_posts` AS p
INNER JOIN `wp_postmeta` AS pm ON p.`ID` = pm.`post_id`
WHERE p.`post_type` = ‘shop_coupon’
AND p.`post_status` = ‘publish’
GROUP BY p.`ID`
ORDER BY p.`ID` ASC;

So now, if you want to automatically create Woocommerce coupon codes that has some typical format such as DISC0001, DISC0002 until DISC0050, we can use 2 steps processes. First we create 50 (you can change to any amount you need) coupon codes using below SQL query:


SET @createQuery = (
SELECT CONCAT('INSERT INTO wp_posts (post_title, post_content, post_excerpt, post_status, post_type, to_ping, pinged, post_content_filtered) VALUES ',
GROUP_CONCAT(CONCAT('(\'DISC', LPAD(code, 4, '0'), '\', \'\', \'\', \'publish\', \'shop_coupon\', \'\', \'\', \'\')') SEPARATOR ','),
';')
FROM (
SELECT (ROW_NUMBER() OVER ()) AS code
FROM wp_posts
LIMIT 50
) AS subquery
);

-- Execute the query to create coupons
SET SESSION sql_mode='';
PREPARE stmt FROM @createQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

Once all the 50 coupons have been created, we can execute below query to apply the coupon settings. You can use the coupon meta keys in the table above to add different settings for the coupons in bulk. In this case, we will apply the following settings for all the newly created DISCXXXX coupons, so that all coupons with code/name that starts with the word ‘DISC’, will have 20% discount and one time use only, also unstackable.

• discount_type = percentage
• coupon_amount = 20
• individual_use = yes
• usage_limit = 1

The SQL for the above settings:

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID, 'usage_limit', '1'
FROM wp_posts
WHERE post_title LIKE 'DISC%';

 

Finally, our coupons are now ready to be used.

Happy testing!

If you have other better way to edit WooCommerce coupon data in bulk, please leave your comment below.