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
– Ronak Shah, Commented Mar 5, 2020 at 3:05

G. Grothendieck · Accepted Answer · 2020-09-30 13:46:04Z

2

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

edited Sep 30, 2020 at 13:46

answered Mar 5, 2020 at 4:05

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dave Costa Over a year ago

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).

G. Grothendieck Over a year ago

Search the page for Why am I having problems with update? to find FAQ 8.

Dave Costa Over a year ago

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.

G. Grothendieck Over a year ago

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.

Edward · Accepted Answer · 2020-03-05 03:27:52Z

0

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

answered Mar 5, 2020 at 3:27

Edward

22.2k3 gold badges18 silver badges37 bronze badges

Collectives™ on Stack Overflow

R delete rows from data table using sqldf

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related