how to update the record with batch in phpmyadmin

[runeveryday]

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.

[r937]

how do i do? thank you.

load the excel data into another table, and then use a joined update, the syntax for which is explained in da manual

[runeveryday]

i am sorry, i don't know how to do it? could you say it more clearly. thank you.how to set the data in the excel? and how to load it? how to use a joined update?

[runeveryday]

anyone helps?

[r937]

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

[runeveryday]

1,the original data is stored in the database as the image shows. the table is products.
2011-11-04_104123.jpg

2, the data in excel is as the following shows.

2011-11-04_094623.jpg
3,but when i open it by notepad++. it shows like this:

2011-11-04_094902.jpg

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, supposed the csv file named test.csv. but the data doesn't be updated.

LOAD DATA  LOCAL INFILE 'D:\\test.csv' REPLACE INTO TABLE products
FIELDS TERMINATED BY '  '
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(products_model, @var1)
SET products_price = IF(@var1 = '', 0, @var1); -- Replace empty string with 0

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

[r937]

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

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

two: how to use joined update?

look it up in the manual

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

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

[runeveryday]

you mean fist i must be create a table, then load the csv date into it then use the separate table to update the products table ?

i am sorry, i don't know how to write the command/

[r937]

you mean fist i must be create a table, then load the csv date into it then use the separate table to update the products table ?

this is what i have been saying all along, yes

[runeveryday]

i got it. many thanks. then using the command like the following

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;