Re: How to update a table with excel sheet or data from another table
huizhang66 wrote:
> I have a table "product" with product code and price, etc.
> And I also have an excel sheet with the same product codes but new prices.
>
> Now I want to be able to update the table "product" with the data in the Excel sheet.
>
> Or I can upload the excel sheet to make a new table "new_product_price", and update the old
> table "product" with new table "new_product_price".
>
> 1) How can I do that? Update the table with Excel sheet.
If you want to work directly from the Excel sheet, you will need something that understands the Excel-format. In Perl (e.g., that's what I use), you could use the Spreadsheet::ParseExcel module and the DBI module to update your table;
> 2) How can I update a table with the data from another table?
On the other hand, using LOAD DATE INFILE on the Excel data saved as CSV would easily create a temporary table that can be used to update the original, as follows:
-- this one holds your original data
CREATE TABLE foo (id INT NOT NULL PRIMARY KEY, price DECIMAL(10,2));
-- this one hold your 'new' data after LOAD DATA INFILE ...
CREATE TABLE tempfoo (id INT NOT NULL PRIMARY KEY, price DECIMAL(10,2));
-- then, to update:
REPLACE foo (id, price) SELECT id, price FROM tempfoo;
DROP TABLE tempfoo;
--
felix