MySQL Forums
Forum List  »  General

Re: How to update a table with excel sheet or data from another table
Posted by: Felix Geerinckx
Date: April 21, 2005 03:39PM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: How to update a table with excel sheet or data from another table
April 21, 2005 03:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.