[R] sqldf syntax
Bond, Stephen
Stephen.Bond at cibc.com
Fri Aug 27 15:30:24 CEST 2010
I had checked those references before posting, actually. SQLite has a very limited implementation of the standard. To do a single table update I would not go to sql. It's easy enough to do in R.
The problem is when I need to do an update from a left outer join, which I had to do with sqlSave (to a mySQL table), then sqlQuery, then sqlFetch.
sqlSave is amazingly slow, takes half an hour. (Would never do that at home :-) just too lazy to write a formal table def and use load data infile from a csv dump.
Also not happy with Dates becoming years in the transition.
Will check the other suggestion about data.table and report.
Cheers everybody.
Stephen B
-----Original Message-----
From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
Sent: Thursday, August 26, 2010 4:26 PM
To: Bond, Stephen
Cc: r-help at r-project.org
Subject: Re: [R] sqldf syntax
On Thu, Aug 26, 2010 at 2:31 PM, Bond, Stephen <Stephen.Bond at cibc.com> wrote:
> Please correct the following
>
>> sqldf("update esc left join forwagg on esc.ym=forwagg.Date set esc.ri2=forwagg.N1 where esc.age=12","select * from main.esc")
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver: (error in statement: near "left": syntax error)
>
1. sqldf takes one sql argument whereas the above has two sql
arguments; however, the one argument may be a vector of sql commands.
See ?sqldf and the examples on the sqldf home page
http://sqldf.googlecode.com
2. there is an error in the syntax of your update statement. For
correct syntax see the sqlite site:
http://sqlite.org/lang_update.html
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
More information about the R-help
mailing list