Hi Cindy
I assume you talk about sqlUpdate() in RODBC
You probably need to set the argument "index" in sqlUpdate, so RODBC knows what
column it should use to update the values (any column that can identify the
rows to be updated).
Here is a small example (here with an MS Access database, but I suppose it
would work with other ODBC databases)
# --------------------------------
tbl <- data.frame( "ID" = 1:10, "VALUE" = rnorm(10) )
con <- odbcConnectAccess( "soils.mdb" ) # Could be any database
sqlSave( channel = con, dat = tbl, tablename = "myNewTable",
rownames = FALSE, append = FALSE )
sqlFetch( channel = con, sqtable = "myNewTable" )
tbl[, "VALUE" ] <- rnorm(10)
# Generate error
sqlUpdate( channel = con, dat = tbl, tablename = "myNewTable" )
# Error in sqlUpdate(channel = con, dat = tbl, tablename = "myNewTable") :
# cannot update 'myNewTable' without unique column
# But this is ok
sqlUpdate( channel = con, dat = tbl, tablename = "myNewTable",
index = "ID" )
sqlFetch( channel = con, sqtable = "myNewTable" )
odbcClose( con )
# --------------------------------
Generally speaking, if you want relevant answers to your questions, it is
always a good idea to say which package you are using and which database you
are working with. Knowing the structure of your table and providing the output
of your sessionInfo() would help too.
All the best
Julien
> -----Original Message-----
> From: r-help-boun...@r-project.org [mailto:r-help-boun...@r-project.org]
> On Behalf Of cindy.dol
> Sent: 29 June 2012 15:52
> To: r-help@r-project.org
> Subject: Re: [R] sqlSave()
>
> Hello,
>
> I have an issue with SqlUpdate and perhaps you can help me.
> I have created a table with sqlSave.
> My table on Access is "PRT".
> My dataframe on R : RESULTS.
> My channel : C2.
> sqlSave(C2, RESULTS, tablename = "PRT",rownames=FALSE, safer = FALSE)
> And it works.
>
> Now I would like to update it, to keep the old values and add new values.
> I tried :
> sqlUpdate(C2, RESULTS, tablename="PRT", append=TRUE) But it doesn't
> work, the error message is :"cannot update ‘PRT’ without unique column"
>
> Do you know how I can do it?
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/sqlSave-
> tp892040p4634881.html
> Sent from the R help mailing list archive at Nabble.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.
______________________________________________
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.
