1

I have a csv with two columns, col1 is a barcode, col2 is stock quantity.

I have the 3 tables. Table1:product_option_value Fields: product_option_value_id, product_option_id, product_id, option_id, option_value_id, quantity, subtract, price, price_prefix, points, points_prefix, weight, weight_prefix

Table2: product_option_newvalue Fields: product_id, product_option_value_id, sku, upc

I am trying to update the QUANTITY field of the table product_option_value using the sku and quantity in my CSV file, the part I’m having trouble with is I have to use product_option_value_id in the product_option_newvalue table to update QUANTITY field in product_option_value, how would I reference between the two?

Here is what I have. It does not work.

CREATE TABLE oc_product_import LIKE oc_product_option_value;
LOAD DATA INFILE '/var/lib/mysql-files/out.csv'
       INTO TABLE oc_product_import 
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       (sku, quantity);
UPDATE oc_product_option_value AS R 
INNER JOIN oc_product_import AS P 
       ON R.product_option_value_id = P.product_option_value_id 

SET R.quantity = P.sku; 
DROP TABLE product_import;"

Edit: my issue seems to be that the barcode is only stored in product_option_newvalue, and can only be linked to quantity by referencing product_option_value_id, in both tables, To update the quantity in the product_option_value table.

EDIT2: This is similar code that is working for me. But it does not have to reference PRODUCT_OPTION_VALUE_ID issue I’m working with in the two table as the barcode is included in the product table and not an additional table by reference

DROP TABLE IF EXISTS oc_product_import; 
CREATE TABLE oc_product_import LIKE oc_product
LOAD DATA INFILE '/var/lib/mysql-files/out.csv'
INTO TABLE oc_product_import 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ';'
(sku, quantity);
UPDATE oc_product AS R 
INNER JOIN oc_product_import AS P 
ON R.sku = P.sku 
SET R.quantity = P.quantity; 
DROP TABLE oc_product_import;"
Joe
  • 45
  • 4
  • The WHERE was just left there while I was brain storming, I added an edit to better explain the logic I’m having trouble understanding. This is a temporary table that will be dropped after updating. – Joe Mar 22 '19 at 02:42
  • What is the error message? – Tim Biegeleisen Mar 22 '19 at 02:44
  • I do not yet have an error code because I have not run the SQL, as I do not understand the logic behind what I’m trying to do, I do have some code I wrote that works but it is only done with two tables, not using the reference of PRODUCT_OPTION_VALUE_ID, I’ll include the working code I have in a second edit. – Joe Mar 22 '19 at 03:35

1 Answers1

1

I would recommend using MySQL Workbench. It isn't the best, but it definitely makes situations like these easier to manage.

After you get it setup you can right click on a table and select table data import wizard and manually add a csv file that way.

If you get any errors with this let me know.

Also, if you are on mac you can simply brew cask install mysqlworkbench in terminal.

Lord Elrond
  • 13,430
  • 7
  • 40
  • 80
  • I’m writing a script to run server side. So using MySQL work bench to load the data will not work. – Joe Mar 22 '19 at 03:03