Thanks Chris.  I am trying almost the same solution while I have failed the
dbWriteTable.

The problem of using update is that it is way TOO slow when the row size is
~200000.

That is why I hope I can still get dbWriteTable way to add one column.
dbWriteTable is very efficient and fast.  The problem of dbWriteTable, so
far I know and so far I have read, is that you have to load one data frame
which covers all the columns of one table.  Now I want to do is bulky load
one column in stead of ALL columns.  Supposedly underneath dbWriteTable is
"load data infile", which according to my reading should allow you to load
data infile to one table column.

can someone help?

Thanks.


On 6/6/07, Chris Stubben <stubben@lanl.gov> wrote:
>
>
> > I have a question reading using RMySQL trying to load one R vector into
> a
> > table column.  To be more specifically, the table is there
> populated.  Now I
> > add a new column and want to populate this.
> >
>
>
> Okay, this is more of an SQL question now, but you could just use
> dbWriteTable
> and then do an multi-table update.
>
>
>
> dbGetQuery(con, "select * from tmp")
>
> id name
> 1  1    A
> 2  2    B
> 3  3    C
> 4  4    D
> 5  5    E
>
>
> dbSendQuery(con, "alter table tmp add column r2 float")
>
> ## calculate some statistic for all or some ids in table
>
>
> x<-dataframe(id=1:5, r2=c(.1, .4, .9, .4,.7))
>
>
> dbWriteTable(con, "r2tmp",  x )
>
>
> dbSendQuery(con, "update tmp t, r2tmp r set t.r2=r.r2 where t.id=r.id")
>
>
> dbGetQuery(con, "select * from tmp")
>
> id name  r2
> 1  1    A 0.1
> 2  2    B 0.4
> 3  3    C 0.9
> 4  4    D 0.4
> 5  5    E 0.7
>
>
> Chris
>
> ______________________________________________
> R-help@stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>



-- 
Waverley @ Palo Alto

	[[alternative HTML version deleted]]

