Dear All, Is sqldf equipped with delete queries? I have tried delete queries but with no success. Thanks in advance, Paul
Yes but delete does not return anything so its not useful. In the devel version of sqldf you can pass multiple command so try this using the builtin data frame BOD noting that the record with demand = 8.3 was removed:> library(sqldf)Loading required package: RSQLite Loading required package: DBI Loading required package: gsubfn Loading required package: proto> # overwrite with devel version of the sqldf.R file > source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R") > sqldf(c("delete from BOD where demand = 8.3", "select * from BOD"))Time__1 demand 1 2 10.3 2 3 19.0 3 4 16.0 4 5 15.6 5 7 19.8 On 9/7/07, Paul Smith <phhs80 at gmail.com> wrote:> Dear All, > > Is sqldf equipped with delete queries? I have tried delete queries but > with no success. > > Thanks in advance, > > Paul > > ______________________________________________ > 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. >
On 9/7/07, Gabor Grothendieck <ggrothendieck at gmail.com> wrote:> Yes but delete does not return anything so its not useful. In the devel > version of sqldf you can pass multiple command so try this using the > builtin data frame BOD noting that the record with demand = 8.3 was > removed: > > > library(sqldf) > Loading required package: RSQLite > Loading required package: DBI > Loading required package: gsubfn > Loading required package: proto > > # overwrite with devel version of the sqldf.R file > > source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R") > > sqldf(c("delete from BOD where demand = 8.3", "select * from BOD")) > Time__1 demand > 1 2 10.3 > 2 3 19.0 > 3 4 16.0 > 4 5 15.6 > 5 7 19.8I see, Gabor, but I would expect as more natural to have sqldf("delete from BOD where demand = 8.3") working, with no second command. Paul> On 9/7/07, Paul Smith <phhs80 at gmail.com> wrote: > > Dear All, > > > > Is sqldf equipped with delete queries? I have tried delete queries but > > with no success. > > > > Thanks in advance, > > > > Paul > > > > ______________________________________________ > > 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. > > >
All sqldf does is pass the command to sqlite and retrieve whatever it sends back translating the two directions to and from R. sqldf does not change the meaning of any sql statements. Perhaps the meaning you expect is desirable but its not how sqlite works. If sqlite were changed to adopt that meaning then sqldf would automatically get it too. Here is an example which does not involve R at all which illustrates that delete returns nothing. C:\> sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> sqlite> create table t1(a,b); sqlite> insert into T1 values(1,2); sqlite> insert into T1 values(1,3); sqlite> insert into T1 values(2,4); sqlite> delete from t1 where b = 2; sqlite> select * from t1; 1|3 2|4 On 9/7/07, Paul Smith <phhs80 at gmail.com> wrote:> On 9/7/07, Gabor Grothendieck <ggrothendieck at gmail.com> wrote: > > Yes but delete does not return anything so its not useful. In the devel > > version of sqldf you can pass multiple command so try this using the > > builtin data frame BOD noting that the record with demand = 8.3 was > > removed: > > > > > library(sqldf) > > Loading required package: RSQLite > > Loading required package: DBI > > Loading required package: gsubfn > > Loading required package: proto > > > # overwrite with devel version of the sqldf.R file > > > source("http://sqldf.googlecode.com/svn/trunk/R/sqldf.R") > > > sqldf(c("delete from BOD where demand = 8.3", "select * from BOD")) > > Time__1 demand > > 1 2 10.3 > > 2 3 19.0 > > 3 4 16.0 > > 4 5 15.6 > > 5 7 19.8 > > I see, Gabor, but I would expect as more natural to have > > sqldf("delete from BOD where demand = 8.3") > > working, with no second command. > > Paul > > > > On 9/7/07, Paul Smith <phhs80 at gmail.com> wrote: > > > Dear All, > > > > > > Is sqldf equipped with delete queries? I have tried delete queries but > > > with no success. > > > > > > Thanks in advance, > > > > > > Paul