sqldf deleting observations glitch

Question

I am using sqldf to delete three observations like this below.

del_query <- sprintf("DELETE from data_table
                      where ID_NUMBER IN (%s)",
                     paste((
                                      '8254700',
                                      '8641472',
                                      '9022163'
                                      ), collapse=", "))

sqlQuery(myconn, del_query)

I dont see any error when I execute the query. I dont get the right results either. Its just deleting the first observations where the ID is 8254700, the remaining two observations where ID is either 8641472 or 9022163 is still present. Wonder what I am doing wrong ...I need all the three observations gone...any help is much appreciated folks.

score 2 · Accepted Answer · edited Oct 20 '15 at 11:27

Assuming you have this data frame:

df <- data.frame(ID = c(8254700, 8641472, 9022163, 1111111, 2222222 ), 
                 field1 = c(1, 2, 3, 4, 5))

giving:

> df
       ID field1
1 8254700      1
2 8641472      2
3 9022163      3
4 1111111      4
5 2222222      5

To delete those three records, you need to add "SELECT * FROM df" or it will return NULL because sqldf always returns the result of the SQL statement given and DELETE does not return a result.

library(sqldf)
sqldf(c("DELETE FROM df WHERE ID IN (8254700, 8641472, 9022163)", "SELECT * FROM df"))

Output:

       ID field1
1 1111111      4
2 2222222      5

If the IDs are in a separate vector, ids, then try this (output is same):

ids <- c(8254700, 8641472, 9022163)
fn$sqldf(c("DELETE FROM df WHERE ID IN (`toString(ids)`)", "SELECT * FROM df"))

or to examine intermediate results:

idString <- toString(ids)
idString
## [1] "8254700, 8641472, 9022163"

sql <- fn$identity(c("DELETE FROM df WHERE ID in ($idString)", "SELECT * FROM df"))
sql
## [1] "DELETE FROM df WHERE ID in (8254700, 8641472, 9022163)"
## [2] "SELECT * FROM df"   

sqldf(sql)
##            ID field1
## 1 1111111      4
## 2 2222222      5

I think OP wants this bit as dynamic string - `(8254700, 8641472, 9022163)` — zx8754, Oct 20 '15 at 08:52
@G. Grothendieck Thank you very much for the thorough editing and major contribution to the answer. I think it is really your answer. — mpalanco, Oct 20 '15 at 11:56
@G. Grothendieck , mpalanco you guys killed it man, that was awesome. :) — King Frazier, Oct 20 '15 at 16:28

sqldf deleting observations glitch

1 Answers1