Waverley
2007-Jun-06 16:44 UTC
[R] 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]]
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