On Sat, Apr 26, 2014 at 10:06 AM, Christopher Lowenkamp <clowenk...@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 ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
