sqldf - SQL UPDATE not working or at least not as expected.

[Scott Ortiz]

I understand that sqldf allows DF's to be manipulated like SQL Tables. It is also used to fetch data from SQLite in the case of a temp or persistent db. So why within a function can I not update a SQLite Table with the contents of a DF?

...
resimrrsummary <- as.data.frame(rbind(summary(resimrr98$TOTALAMT))) # a simple summary function cast as a dataframe within my function

colnames(resimrrsummary) <- c("min", "first_qu", "median", "mean", "third_qu", "max") # I reworked the column names because I was getting errors with column names like '1st Qu.'"

Below "Summary" is Table in "mydb"

sqldf("update Summary
set min = resimrrsummary$min,
first_qu = resimrrsummary$first_qu,
median = resimrrsummary$median,
mean = resimrrsummary$mean,
third_qu = resimrrsummary$third_qu,
max = resimrrsummary$max
where segment = 'ResiMRR98'", dbname="mydb")

Error: RS-DBI driver: (error in statement: no such column: resimrrsummary$min).

In my function just prior to this sqldf call I print the contents of resimrrsummary and all looks good.

Stumped???

[Gabor Grothendieck]

The $ sign in the sql statement in the question is an R operator - not an sql operator. The first argument to sqldf must be an sql statement and it must follow sql syntax.  Normally sqldf is used for handling data frames as sql tables and if we want to deal with existing sqlite databases one would go directly to RSQLite but assuming we want to use sqldf here anyways here is some code to play around with.

> library(sqldf)

> 

> # create mydb and populate it with a summary table for reproducibility

> summary_ws <- data.frame(min_value = 1:3, max_value = 4:6, other = 7:9)

> sqldf("attach 'mydb' as new")

NULL

> sqldf("create table Summary as select * from summary_ws", dbname = "mydb")

NULL

> 

> # create res data frame in R work space

> res <- data.frame("min_value" = 0, "max_value" = 1)

 

> # check that Summary is in mydb

> sqldf("select * from Summary", dbname = "mydb")

  min_value max_value other

1         1         4     7

2         2         5     8

3         3         6     9

> 

> # form and run sql statement

> sql <- sprintf("update Summary set min_value = %d, max_value = %d",

+ res$min_value, res$max_value)

> sql

[1] "update Summary set min_value = 0, max_value = 1"

> sqldf(sql, dbname = "mydb") # run it

NULL

> 

> # check that Summary has been updated

> sqldf("select * from Summary", dbname = "mydb")

  min_value max_value other

1         0         1     7

2         0         1     8

3         0         1     9

[Scott Ortiz]

Crazy good stuff Gabor, thank you!  Do you mind if I post your thoughts on StackOverflow?  I posted this inquiry there as well (NM, I just checked and saw your redirect there too).

-Scott

[Gabor Grothendieck]

I have already added a comment to your post there to see this group. I would prefer that discussion of sqldf be focused on this group.