[R] [R-sig-DB] Update results not being written to existing data frame when using sqldf UPDATE

Gabor Grothendieck 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:
> sqldf
> gsubfn
> proto
> RSQLite
> DBI
> RSQLite.extfuns
> tcltk
>
> 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.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com




More information about the R-help mailing list