[R] Delete query in sqldf?

[Gabor Grothendieck]

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