Updating stock levels from a CSV file or another table

[ollyno1uk]

Hi there

I have a table in MySQL database on my web server that contains stock levels amongst other things. twice a day I get emailed an updated stock list in CSV format.

What I need is to update the stock quantities in my table with the values from the CSV file.

I have managed to successfully upload the CSV file into a different table I generated but I have no idea how to get the stock amounts updated.

The CSV file and the table have a product ID field which contains the unique reference to the product. In the CSV file there are products I do not list and in my table there are products that are not in the CSV file. These need to be ignored.

I need to somehow be able to tell it to look for the product ID and where it matches fill with the updated stock quantities.

Can anyone help me with this?

Thanks a lot.

[ronverdonk]

Maybe I don't understand your problem correctly, but is this what you are looking for?

UPDATE Product, Csv SET Product.stock = Csv.stock 
WHERE Product.Id = Csv.Id

Ronald :cool:

[gfcampbell]

I am using MYSQL 3.23 and I get this message when trying a multi table update.
Does this only work on 4.0 or higher? Or do the fields have to be defined the same?

mysql> update test_cpn, acct_cpn set test_cpn.servic e=acct_cpn.acct where acct_cpn.cpn=te st_cpn.coupon;
ERROR 1064: You have an error in your SQL syntax near ' acct_cpn set test_cpn.servic e=acct_cpn.acct where acct_cpn.cpn=te st_cpn.coupon' at line 1

[masdi2t]

yeah, you can take an advantage of update on multiple table with MySQL 4.0.4 and up