Waverley

2007-Jun-06 16:44 UTC

### Question: RMySQL bulk load/update one column, dbWriteTable()?

Hi, 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. Can some colleagues on this list teach me how to do this? I know how to write one R object/table into MYSQL table using dbWriteTable. But in this situation, I just want to do one column. Thanks much in advance. -- Waverley [[alternative HTML version deleted]] ______________________________________________ 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.

Chris Stubben

2007-Jun-06 20:47 UTC

### [R] Question: RMySQL bulk load/update one column, dbWriteTable()?

> 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

Waverley

2007-Jun-06 21:54 UTC

### Re: Question: RMySQL bulk load/update one column, dbWriteTable()?

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]] ______________________________________________ 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.