How to update record in R - problem with sqldf

user_17580494 0
Viewed 44

I would like to change some records in my table. I think the easest way is to use sqldf and Update. But when i using it i get warning (the table b isn't empty):

c<-sqldf("UPDATE b
          SET l_all = ''
          where id='12293' ")

# In result_fetch(res@ptr, n = n) :
# SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().

Can you help me how to change chosen records in the easest way?

dbi r warnings sql
1 Answers

The query worked but there are several possible problems:

  1. The message is a spurious warning, not an error, caused by backwardly incompatible changes to RSQLite. You can ignore the warning or use the sqldf2 workaround here: https://github.com/ggrothendieck/sqldf/issues/40
  2. The SQL update command does not return anything so one would not expect the command shown in the question to return anything. To return the updated value ask for it.

1) Using the built in BOD data frame, defining sqldf2 from (1) and taking into account (2) we have:

sqldf2(c("update BOD set demand = 0 where Time = 1", "select * from BOD"))

giving:

  Time demand
1    1    0.0
2    2   10.3
3    3   19.0
4    4   16.0
5    5   15.6
6    7   19.8

2) Another approach to do it is to use select giving the same result.

sqldf("select Time, iif(Time == 1, 0, demand) demand from BOD")
user_5165480
Related