Bulk Edit & Create WooCommerce Coupons from Database using PhpMyadmin

Bulk Edit & Create WooCommerce Coupons from Database using PhpMyadmin

bulk edit woocommerce coupon

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:

meta_key meta_value
discount_type percentage/fixed_cart/fixed_product
coupon_amount numbers e.g: 0,1,2……..
free_shipping yes/no
expiry_date date based on your wp format. e.g: 2018-10-28
minimum_amount numbers e.g: 0,1,2…..
maximum_amount numbers e.g: 0,1,2…..
individual_use yes/no
exclude_sale_items yes/no
product_ids product ids e.g: 5674,8765,37265…….
exclude_product_ids product ids e.g: 5674,8765,37265…….
product_categories variable with cat ids e.g: a:3:{i:0;i:204;i:1;i:199;i:2;}
exclude_product_categories variable with cat ids e.g: a:3:{i:0;i:204;i:1;i:199;i:2;}
customer_email variable with user email e.g: a:1:{i:0;s:14:”email@mail.com”;}
usage_limit numbers, 0=unlimited.
usage_limit_per_user numbers, 0=unlimited.
usage_count numbers

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.

woocommerce coupon bulk edit

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:


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’


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 wp_posts
) AS subquery

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


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, 'discount_type', 'percent'
FROM wp_posts
WHERE post_title LIKE 'DISC%';

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

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

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.

Share this post

Open chat
Powered by