R help - Jun 2007 - 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]]

> 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