Updating Woocommerce Coupon descriptions mysql

Question

in my database this gives me the correct coupon results im looking for

SELECT p.`ID`, p.`post_title`, p.`post_excerpt`
FROM   `wp_posts` AS p
INNER JOIN `wp_postmeta` AS pm ON  p.`ID` = pm.`post_id`
WHERE  p.`post_type` = 'shop_coupon'
AND pm.`meta_key` = 'product_ids'
AND pm.`meta_value` = '131239'

but i need to update all these coupons its over 5k in coupons and the standard import/merge is not updating the coupon description, its essential that our coupons have the correct description due to our accounting system so i need to update them by sql

UPDATE p SET p.post_excerpt = 'emesa'
FROM wp_posts AS p
INNER JOIN wp_postmeta AS pm
ON p.ID = pm.post_id
WHERE p.post_type = 'shop_coupon'
AND pm.meta_key = 'product_ids'
AND pm.meta_value = '131239'

using update set does not work.

What details do you need to update ? What is the logic behind it ? — Madhur Bhaiya, Sep 25 '18 at 16:55
i need to update the product description which is in the table under post_excerpt — Brad Holmes, Sep 25 '18 at 16:57
What is the update condition ? What is the update value ? Under which conditions update can happen ? — Madhur Bhaiya, Sep 25 '18 at 16:58
try this - `UPDATE p SET p.post_excerpt = 'emesa' FROM wp_posts AS p INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id WHERE p.post_type = 'shop_coupon'` ?? — Madhur Bhaiya, Sep 25 '18 at 16:58
i need to set all coupons descriptions from the coupons with the product_ids of 131239 to emesa — Brad Holmes, Sep 25 '18 at 17:00
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_posts AS p INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id WHERE p.pos' at line 1 — Brad Holmes, Sep 25 '18 at 17:01

Madhur Bhaiya · Accepted Answer · 2018-09-25T17:06:14.987

Update query syntax is different from the Select query. In Update query, you first specify all the tables (including joins) and then use Set clause. There is no usage of From in the Update query.

From the MySQL Documentation:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

value: {expr | DEFAULT}

assignment: col_name = value

assignment_list: assignment [, assignment] ...

Following will be the correct query:

UPDATE wp_posts AS p
INNER JOIN wp_postmeta AS pm
ON p.ID = pm.post_id 
SET p.post_excerpt = 'emesa'
WHERE p.post_type = 'shop_coupon'
AND pm.meta_key = 'product_ids'
AND pm.meta_value = '131239'

Updating Woocommerce Coupon descriptions mysql

1 Answers1