the table named: `products`. it have five hundred rows record. the table have field :`products_model` `products_price`. the `products_model` is unique。

the original value that stored in `products` table are all `0.0000`. now i want to update it with batch in phpmyadmin according to the 'products_model`.

i prepared the data in `excel` with two columns, 'products_model` the new value `products_price`(according to some reason, some may be empty). how do i do? thank you.

the origina data :

start with your excel file

save it as a tab-separated csv file

then look up LOAD DATA INFILE in the mysql manual

this will help you load the excel data into a mysql table

that should get you started

once this is ready, you will use a joined update to update your main table

the syntax for this is explained in the manual under UPDATE

1, what's the tab-separated csv file. does it like this?

2,supposed the file named product.csv, when using LOAD DATA INFILE 'product.csv'
where should i put the csv file into? i put it on my local computer, how do i load?

1. sort of, yes, assuming you have tabs between the fields
2. into a table that you declare, that has two columns, to receive the fields from the csv

1,the original data is stored in the database as the image shows. the table is products.
http://www.dbforums.com/attachment.p...1&d=1320374610

2, the data in excel is as the following shows.
http://www.dbforums.com/attachment.p...1&d=1320373322

3,but when i open it by notepad++. it shows like this:

http://www.dbforums.com/attachment.p...1&d=1320373367

one: the data's format which i prepared is right? if not? how to correct it?

two: how to use joined update?

three: i using this command in phpmyadmin, but the data doesn't be updated.



the command can run, but the data doesn't be updated. thank you.

did you save it from excel as a tab-delimited csv?

look it up in the manual

you're not supposed to load the csv into your main table, you're supposed to load the csv into a separate table, and then use the separate table to update the products table

When defining the LOAD INTO command use '\t' for a tab:

Also using REPLACE will replace the entries that already exist based on the primary key. While this works, I would do what Rudy is suggesting which is load the data into a staging table and then use an UPDATE statement in order to migrate the data to the live table. I am speaking from experience where the load process or file format changes between runs and then you can end up with garbage and nothing to fall back on.

1,i don't know how to save the csv file from excel as a tab-delimited csv?

2, you mean i must create a new table then stored the csv data in it. then using it to update the products table.

i am very sorry, it hard for me to write the sql command.

when i used REPLACE it didn't replace the entries that already exist, it inserted new lines into the products table, i don't know why?

in the products table, the products_id is the primary key. but products_model is also unique.but not the primary key.

3, how to use UPDATE to update the products table with the staging table?

it's a pretty common thing to do

maybe start a thread in some excel forum?

maybe press F1 (Help) when you're in excel, and look it up?


this is what i have been saying all along, yes



i keep telling you, look it up in the mysql manual, under the UPDATE statement