CODEWITHSUNDEEP
sqlrsqldf
King Frazier
King Frazier

Reputation: 343

sqldf deleting observations glitch

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.

Upvotes: 2

Views: 259

Answers (1)

mpalanco
mpalanco

Reputation: 13580

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

Upvotes: 2

Related Questions