mysql insert update LOAD DATA LOCAL INFILE

Question

i am using LOAD DATA LOCAL INFILE to load data into temp table mid.then i use a update query to update found in table product.The only matching field in both is the model.

$q  = "LOAD DATA LOCAL INFILE 'Mid.csv' INTO TABLE mid 
    FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'  IGNORE 1 LINES 
    (@col1,@col2,@col3,@col4,@col5,@col6) set model=@col1,price=@col3,stock=@col6 ";
mysql_query($q, $db);


mysql_query('UPDATE mid m, products p set p.products_price= m.price,p.products_quantity= m.stock where p.products_model= m.model');

It works and update the product table.the issue i am having is that there new records in mid table which don't get inserted as i am using the update statement.

I have looked at the insert query and update on duplicate.I have seen loads of examples of when it has to work on one table but none where i have to match it against another table.

Either i am searching for the wrong thing or there is another way to to do this.

i would appreciate any help.

regards

naf

Ike Walker · Accepted Answer · 2013-08-20 00:24:43Z

1

I'm not sure what the other columns in the product table are, but here's a basic approach that should work for you based on the 3 columns in your example, assuming the products_model column is unique in the products table:

insert into products (products_price,products_quantity,products_model)
select price, stock, model
from mid
on duplicate key update
  products_price = values(products_price),
  products_quantity = values(products_quantity)

answered Aug 20, 2013 at 0:24

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

naf Over a year ago

I tried the above but it still doesn't insert. product table has over 14 columns.does it make a difference.

Ike Walker Over a year ago

If you want to insert new rows into the product table then you'll need to be able to provide proper values for all of the columns. Does the CSV have the data you need? I know it has at least 6 columns based on your code, so maybe if you load the entire CSV into a table that will give you the data you need.

naf Over a year ago

product table has loads of fields I only wish to update products_price,products_quantity,products_model.The csv also has quite a few fields but i am only interested in price, stock, model.If a model doesnt exit then i want it to insert products_price,products_quantity,products_model in products table and write null in other corresponding coloumns.

Abhishek Gupta Over a year ago

It works for me with a slight augmentation to the above query i.e select on all the columns that are in the insert list.

Collectives™ on Stack Overflow

mysql insert update LOAD DATA LOCAL INFILE

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related