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

[Chris Stubben]

> 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