[R-sig-DB] Update results not being written to existing data frame when using sqldf UPDATE
Christopher Lowenkamp
c|owenk@mp @end|ng |rom gm@||@com
Sat Apr 26 16:06:44 CEST 2014
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]]
More information about the R-sig-DB
mailing list