CODEWITHSUNDEEP
rsqldf
vicky
vicky

Reputation: 415

Using UPDATE statement in sqldf in R

I know it's easier to use base R to update value, but I have to do it in sqldf. I have a data frame, and I want to update a field based on conditions. If column2 is 'c', then update the value in column1.

column1 <- c(1,2,3,4)
column2 <- c("a","b","c","d")
temp <- data.frame(column1,column2)
sqldf("
      UPDATE temp
      set column1=100
      WHERE column2 ='c'
      ")

above code give me "data frame with 0 columns and 0 rows" and warning message "In result_fetch(res@ptr, n = n) : SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery()." The result is the table is never updated/changed.

Thanks a lot for your valuable time.

Upvotes: 1

Views: 2536

Answers (1)

r2evans
r2evans

Reputation: 160447

This is both known and intentional: sqldf never tries to update objects in the environment. It merely acts as a conduit by copying data into a sqlite (temp) database and running the query, never does the package intend to overwrite the source objects in the calling environment.

Reference FAQ 8 in the sqldf README:

Although data frames referenced in the SQL statement(s) passed to sqldf are automatically imported to SQLite, sqldf does not automatically export anything for safety reasons. Thus if you update a table using sqldf you must explicitly return it as shown in the examples below.

Using your data:

sqldf(c("
       UPDATE temp
       set column1=100
       WHERE column2 ='c'
       ", "select * from main.temp"))
# Warning in result_fetch(res@ptr, n = n) :
#   SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().
#   column1 column2
# 1       1       a
# 2       2       b
# 3     100       c
# 4       4       d

(You can safely ignore that warning, it's because the first statement returns nothing.)

So to actually use this to update the object in-place in your environment, you'd need to capture the output yourself.

temp
#   column1 column2
# 1       1       a
# 2       2       b
# 3       3       c
# 4       4       d
temp <- suppressWarnings(sqldf(c("
      UPDATE temp
      set column1=100
      WHERE column2 ='c'
      ", "select * from main.temp")))
temp
#   column1 column2
# 1       1       a
# 2       2       b
# 3     100       c
# 4       4       d

Upvotes: 2

Related Questions