How to insert 2 new columns based on value in existing column in DB

Cosmic_Cake
Cosmic_Cake used Ask the Experts™
on
I want to insert 2 new columns to an already existing/populated table in my database.

I have 2 columns in an excel file, the columns are called 'weight' and 'color'. The condition is that the values in both these columns are associated with the value in a column called 'model_number' (this is unique, the value in this column is not replicated).
The column 'Model_Number' is also in the excel file.

My excel file is organised in such a way that it should be easy enough to execute this query.

Excel file sample:
Column Weight     Column Color    Column Model_Number (this column is already in DB)
3kg                        Red                     4545
5kg                        Green                 8789
2KG                       Blue                    7987
3KG                       Blue                    4332

So for example where the value in Column 'Model_Number' is 4545 the value 3kg will be added to Column Weight and the value Red will be add to Column Color in the DataBase.

What SQL code do I need to use here?
Do I have to add the 2 new columns first before adding the values?
Also I am concerned that by inserting these 2 new columns and adding values that I will reset the 'timestamp' value column in each row which I don't want to do. Is there a way to keep the original timestamp value?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
What SQL code do I need to use here?
I would export the excel sheet to csv then load the data first to a temporary table. Create that table, populate it via LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.1/en/load-data.html).  Then execute this SQL:

update target_table t, loaded_temp_table l
set t.weight_col = l.weight_col, t.color_col=l.color_col
where t.model_number=l.model_number;

Do I have to add the 2 new columns first before adding the values?

Yes.  You can add them by executing "alter table tablename add weight decimal(10,2), color varchar(20)".  Note that I assumed datatypes here - and I suggest you upload the weights without the KG (I assume they are all in kg) so that they can easily be used for computations later on.

Also I am concerned that by inserting these 2 new columns and adding values that I will reset the 'timestamp' value column in each row which I don't want to do. Is there a way to keep the original timestamp value?

Temporarily disable the timestamp auto update by changing the timestamp column definition.  Do an "alter table tablename modify timestamp_column null default null", insert your data, then do another alter table to put back your timestamp column settings.

Author

Commented:
Thanks for your reply, it makes sense and it's a great answer and I will accept it as a solution. I'm just having a bit of trouble with finding a simple example with LOAD DATA INFILE.
Top Expert 2012

Commented:
If you're having problems with LOAD DATA INFILE post a new related question showing what you have tried, including a file containing the sample csv data.  Thanks.

Author

Commented:
ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial