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

From: Waverley <waverley.paloalto_at_gmail.com>
Date: Wed, 06 Jun 2007 14:54:08 -0700

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_at_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_at_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_at_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.
Received on Wed 06 Jun 2007 - 22:49:12 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Wed 06 Jun 2007 - 23:31:34 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.