[R] [R-sig-DB] Update results not being written to existing data frame when using sqldf UPDATE
ggrothendieck at gmail.com
Sat Apr 26 16:19:15 CEST 2014
On Sat, Apr 26, 2014 at 10:06 AM, Christopher Lowenkamp
<clowenkamp at gmail.com> wrote:
> RStudio Version 0.98.501
> R version 3.1.0
> Mac OSX 10.9.2
> Packages loaded:
> Good morning:
> I am trying to run an sqldf update with two tables. Both tables contain a
> variable called ‘off_id’. I am trying to update a variable in tablea (v2)
> with the number of times each record in tablea appears in tableb.
> tablea <- data.frame(off_id = c(12, 14, 16, 17, 18, 22, 1, 5, 7, 44, 4, 3),
> v2 = 0)
> tableb <- data.frame(off_id = c(12, 12, 14, 14, 14, 14, 16, 17, 12, 12, 1,
> 18, 18, 5, 7, 3, 16, 1, 1, 3, 3, 3, 1))
> sql1 <- "UPDATE tablea SET v2 = (SELECT count(*) FROM tableb
> WHERE tableb.off_id = tablea.off_id)"
> sql2 <- “SELECT * FROM tablea”
> #The following code returns "NULL"
> sqldf(sql1, sql2)
> #When I run the following I do get back the data but tablea$v2 still does
> not update
> sqldf(c(sql1, sql2), method = "raw")
> #If I run the following I get the expected results in tablec$v2, but
> tablea$v2 does not update
> tablec <- as.data.frame(sqldf(c(sql1, sql2)))
> I am wondering what I am doing wrong. Is there a way to get tablea$v2 to
> update? I did check at https://code.google.com/p/sqldf/ (and have read
> through FAQ 8 a number of times) but don't see an answer to the problem I
> am having.
sqldf never modies any object in your R work space. It did update the
table in the main sqlite database but its up to you if you want to
write it back to R.
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
email: ggrothendieck at gmail.com
More information about the R-help