R delete rows from data table using sqldf

user_11365546 0
Viewed 1277

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

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.

sqldf r
2 Answers

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

user_5165480

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
user_75145270
Related