R delete rows from data table using sqldf

Question

I am wondering if R does not support using sqldf to delete rows from a data table. My data looks like this

and I am trying to delete from a data table using a delete statement. There is no underlying database just a data.table. But hwen I enter the following sql statement:

loans_good <- sqldf("Delete from LoansDT1 where status not in ('Current','Default')")

I get the following error message:

'SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().'

Since I get the same message for update I am wondering if it is a limitation.

Any specific reason you want to use `sqldf` here? I think this could be done directly using R's standard subsetting operations. — Ronak Shah, Mar 05 '20 at 03:05

G. Grothendieck · Accepted Answer · 2020-09-30T13:46:04.393

This question is a FAQ. See FAQ 8 on the sqldf github home page.

The operation did work. The message is a warning message, not an error message. The message is misleading and you can ignore it. Note that question did not show the complete message -- the complete message does state that it is a warning message.

The warning message comes from RSQLite, not from sqldf itself. It is caused by non-backwardly compatible change that was introduced into RSQLite at some point; however, as stated the actual operation works anyways.

Also delete and update act on tables in the database. They do not return values so even if they work you won't see any result. If you want a result you have to use a select statement after the delete or update to retrieve the modified table.

Here is an example using the built-in 6 row BOD data.frame. It deletes the last row as that row has a Time greater than 5.

library(sqldf)

sqldf(c("delete from BOD where Time > 5", "select * from BOD"))
##   Time demand
## 1    1    8.3
## 2    2   10.3
## 3    3   19.0
## 4    4   16.0
## 5    5   15.6
## Warning message:
## In result_fetch(res@ptr, n = n) :
##   SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().

Note that this is listed in the sqldf issues where a workaround for the message is provided: https://github.com/ggrothendieck/sqldf/issues/40

I don't see how this relates to FAQ 8 on the linked page (or any of the other FAQs, although I scanned through them pretty quickly). — Dave Costa, Sep 29 '20 at 19:12
Search the page for *Why am I having problems with update?* to find FAQ 8. — G. Grothendieck, Sep 29 '20 at 21:20
The FAQ seems to relate to the secondary portion of your answer, about how to actually see the results of an update or delete. I thought since you started your answer with a reference to the FAQ, it was supposed to relate to the warning from RSQLite. — Dave Costa, Sep 30 '20 at 13:27
The key problem is not not seeing the result or the message. The key problem is that the code in the question has no effect at all because nothing is returned in the absence of adding a `select`. — G. Grothendieck, Sep 30 '20 at 13:44

score 0 · Answer 2 · answered Mar 05 '20 at 03:27

You need to use dbExecute() to perform delete, update or insert queries.

conn <- dbConnect("Put your connection to your database here")

dbExecute(
  conn,
  "Delete from LoansDT1 where status not in ('Current','Default')"
)

dbReadTable(conn, LoansDT1) # Check

R delete rows from data table using sqldf

2 Answers2