Christopher Lowenkamp
2014-Apr-26 14:06 UTC
[R] Update results not being written to existing data frame when using sqldf UPDATE
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. Thanks for your time and consideration. Christopher Lowenkamp Administrative Office US Courts University of Missiouri-Kansas City [[alternative HTML version deleted]]
Gabor Grothendieck
2014-Apr-26 14:19 UTC
[R] [R-sig-DB] Update results not being written to existing data frame when using sqldf UPDATE
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