asked on

Updating a table by a reference ID.

In the code below I am using a CSV file and creating a temporary table, and then using an inner join to update a field where the SKU is matching.

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;"

Open in new window

This works fine for me my current purposes. But now I have two tables, TABLE1 has the QUANTITY, and an ID. TABLE2 has an ID, and a SKU. I want to update the quantity using the SKU’s from my CSV as i’ve done before but have to reference it by this ID in another table. That’s the part I don’t know how to do. If my question is confusing please ask a follow-up.

Darren

Hi,

The Syntax for SQL Server is slightly different:

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

Open in new window

Taken from https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql

Thanks,

Darren

jxm90

ASKER

I understand how to do an INNER JOIN, I guess I would be trying to do a double inner join with 3 tables.

TABLE1
sku, qty

TABLE2
id, qty

TABLE3
sku, id

I’m loading the values into TABLE1 from CSV
Then I want to set the quantity in TABLE2 using the sku, and quantity in TABLE1, but the which sku goes to what id, is held in TABLE3.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial