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_modelthe new valueproducts_price`(according to some reason, some may be empty). 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
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?
anyone helps?
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
1,the original data is stored in the database as the image shows. the table is products.
2, the data in excel is as the following shows.
3,but when i open it by notepad++. it shows like this:
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:\\\ est.csv' REPLACE INTO TABLE products
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\\r\
'
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.
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
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/
this is what i have been saying all along, yes
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;