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]]
______________________________________________ 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.
