[R] sqlSave()

[Julien Moeys]

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-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
> On Behalf Of cindy.dol
> Sent: 29 June 2012 15:52
> To: r-help at 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 at 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.