MySQL Forums
Forum List  »  Newbie

Re: Add one new column and populate?
Posted by: Gordon Royle
Date: May 17, 2005 08:45AM

Felix Geerinckx wrote:
> 1) import your file into a temporary table tfoo
> containing the primary key and the extra piece of
> data
> 2) update your original table with
>
> UPDATE foo SET newcol = (SELECT newcol FROM tfoo
> WHERE tfoo.id = foo.id);


I tried this, but it just gave me a syntax error every time... I am not sure if I am using an excessively old version (4.0.17 but I will get it updated first thing tomorrow) of mySQL or whether something more fundamental is wrong.

However, I eventually worked out that if I did

UPDATE foo, tfoo SET foo.newcol=tfoo.newcol WHERE foo.id=tfoo.id;

then I could use your general technique (ie import as new table, copy data over).

Trouble is that I don't know how long it takes... it still hasn't finished though I started it a long time ago - in any case it seems vastly slower than just re-creating the original text data file, and then adding the new column at the end, and reloading the whole thing with LOAD DATA INFILE.

Strange that such a natural seeming thing to do is apparently so hard..

Options: ReplyQuote


Subject
Written By
Posted
Re: Add one new column and populate?
May 17, 2005 08:45AM


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.