Re: Add one new column and populate?
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..
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.