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

[Christopher Lowenkamp]

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