Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Woocommerce – MYSQL: Update Database row Using SELECT and multiple JOINS

Essentially, I’m trying to repair a broken WooCommerce database for a client.

I have a table wp_woocommerce_order_itemmeta and all of the _product_id values are erased. I am able to get the correct ID using this select statement. It will return all of the IDs, one per row as wanted.

SELECT
  wp_wc_order_product_lookup.product_id
FROM
  wp_woocommerce_order_items
INNER JOIN
  wp_woocommerce_order_itemmeta
    ON 
      wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
INNER JOIN
  wp_wc_order_product_lookup
    ON
      wp_wc_order_product_lookup.order_id = wp_woocommerce_order_items.order_id
WHERE
  wp_woocommerce_order_itemmeta.meta_key = '_product_id'

Now when I tried to write an update statement I can’t get it to work. I’ve tried it several different ways. I read somewhere that SET has to be moved past the INNER JOIN lines. 

UPDATE
  wp_woocommerce_order_itemmeta
INNER JOIN 
  wp_woocommerce_order_itemmeta
    ON 
      wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
INNER JOIN
  wp_wc_order_product_lookup
    ON
      wp_wc_order_product_lookup.order_id = wp_woocommerce_order_items.order_id
SET
  wp_woocommerce_order_itemmeta.meta_value = wp_wc_order_product_lookup.order_id
WHERE
  wp_woocommerce_order_itemmeta.meta_key = '_product_id'

That code produces the following error [Err] 1066 - Not unique table/alias: 'wp_woocommerce_order_itemmeta'

wp_woocommerce_order_itemmeta

<code>wp_woocommerce_order_itemmeta</code>

wp_wc_order_product_lookup

<code>wp_wc_order_product_lookup</code>

Tags:

2

Answers


  1. 0

    Perhaps you’re after something like this:

     UPDATE wp_woocommerce_order_itemmeta x
  JOIN wp_woocommerce_order_items y
    ON y.order_item_id = x.order_item_id
  JOIN wp_wc_order_product_lookup z
    ON z.order_id = y.order_id
   SET x.meta_value = z.order_id
 WHERE x.meta_key = '_product_id'
    Login or Signup to reply.
  2. 0

    you can solve this issue with simple PHP script, here is logic
    1) select unique product_id and order_id from wp_wc_order_product_lookup
    2) Result set from step 1 is used to update the table wp_woocommerce_order_itemmeta with the help of looping

    Let me know if face any issue to write script in PHP then I will provide to code.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search