0

I have 5000+ products in WooCommerce and I'm looking for a SQL query to set their barcodes based on their SKU. Both "barcode" and "sku" are custom posts meta. Basically, something like:

UPDATE wp_postmeta SET barcode='x' WHERE sku='y';

But it doesn't work because barcode and sku are not real table columns. So, can anyone provide a working query? I'm new to SQL. Thank you!

dipmala
  • 2,003
  • 1
  • 16
  • 17
otpabu
  • 87
  • 1
  • 4
  • 10
  • 1
    How you will set barcode and sku ? because each product must have different once. – dipmala Sep 19 '18 at 12:36
  • Are you executing these one at a time? Obviously the sku and barcode are two separate rows in wp_postmeta, linked by post_id, but [mysql won't let you query the table you're updating](https://stackoverflow.com/q/10359534/243245) e.g. `UPDATE wp_postmeta ... WHERE post_id = (SELECT post_id FROM wp_postmeta WHERE ...`. So you may have to run two queries: one to look up the post_id from meta_key='sku' and meta_value='y', and then one to update wp_postmeta where meta_key='barcode' and post_id is the one you've just found. – Rup Sep 19 '18 at 12:44
  • @dipmala I have an excel file with all the SKUs and barcodes (unique for each product, of course). I would just copy-paste them into the query. – otpabu Sep 19 '18 at 12:48

1 Answers1

2

The sku and barcode will be stored in two separate rows linked by post_id. This is the syntax you'll need to use to update one row based on another in the same table in MySQL (borrowed from this answer):

UPDATE  wp_postmeta a
  JOIN  wp_postmeta b on b.post_id = a.post_id
  SET   a.meta_value = 'y'
  WHERE a.meta_key = 'barcode'
    AND b.meta_key = 'sku'
    AND b.meta_value = 'x';

i.e. query the ID on table alias b and then update the barcode on table alias a.

(but I don't have a WooCommerce installation to test this on - please backup your DB first! etc.)

Rup
  • 33,765
  • 9
  • 83
  • 112