R help - Mar 2012 - RSqlite UPDATE command problem

All:

I am using RSqlite and want to be able to update individual values in a
record, such as with this simple example:

library(RSQLite)
drv<-dbDriver("SQLite")
con<-dbConnect(drv,"test.db")
my.data<-data.frame(countries=c("US","UK","Canada","Australia","NewZealand"),vals=c(52,36,74,10,98))
dbWriteTable(con,"testtable",my.data)
q<-dbReadTable(con,"testtable")
q

   countries vals
1         US   52
2         UK   36
3     Canada   74
4  Australia   10
5 NewZealand   98

So, say, I want to change the value for NewZealand to '21' from
'98'

I've tried something like this:

sql<-"UPDATE testtable SET vals=21 WHERE
countries='NewZealand'"
dbBeginTransaction(con)
dbGetPreparedQuery(con,sql) <========== I get an error here
dbCommit(con)

using a different example for an INSERT command using a data frame
'data',
this construct is accepted:

dbGetPreparedQuery(con,sql,bind.data=data)

What do I need to do differently to use the UPDATE command?

Regards,
Tom


-- 

Thomas E Adams
National Weather Service
Ohio River Forecast Center
1901 South State Route 134
Wilmington, OH 45177

EMAIL:	thomas.adams@noaa.gov
VOICE:	937-383-0528
FAX:	937-383-0033

	[[alternative HTML version deleted]]

You probably want:

sql<-"UPDATE testtable SET vals=21 WHERE
countries='NewZealand'"
dbGetQuery(con, sql)

instead...

b

On 27 March 2012 14:18, Thomas Adams <thomas.adams@noaa.gov> wrote:
> All:
>
> I am using RSqlite and want to be able to update individual values in a
> record, such as with this simple example:
>
> library(RSQLite)
> drv<-dbDriver("SQLite")
> con<-dbConnect(drv,"test.db")
>
>
my.data<-data.frame(countries=c("US","UK","Canada","Australia","NewZealand"),vals=c(52,36,74,10,98))
> dbWriteTable(con,"testtable",my.data)
> q<-dbReadTable(con,"testtable")
> q
>
>   countries vals
> 1         US   52
> 2         UK   36
> 3     Canada   74
> 4  Australia   10
> 5 NewZealand   98
>
> So, say, I want to change the value for NewZealand to '21' from
'98'
>
> I've tried something like this:
>
> sql<-"UPDATE testtable SET vals=21 WHERE
countries='NewZealand'"
> dbBeginTransaction(con)
> dbGetPreparedQuery(con,sql) <========== I get an error here
> dbCommit(con)
>
> using a different example for an INSERT command using a data frame
'data',
> this construct is accepted:
>
> dbGetPreparedQuery(con,sql,bind.data=data)
>
> What do I need to do differently to use the UPDATE command?
>
> Regards,
> Tom
>
>
> --
>
> Thomas E Adams
> National Weather Service
> Ohio River Forecast Center
> 1901 South State Route 134
> Wilmington, OH 45177
>
> EMAIL:  thomas.adams@noaa.gov
> VOICE:  937-383-0528
> FAX:    937-383-0033
>
>        [[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.
>
	[[alternative HTML version deleted]]