update an SQL table via R sqlSave

Question

I have a data frame in R having 3 columns, using sqlSave I can easily create a table in an SQL database:

channel <- odbcConnect("JWPMICOMP")
sqlSave(channel, dbdata, tablename = "ManagerNav", rownames = FALSE, append = TRUE, varTypes = c(DateNav = "datetime"))
odbcClose(channel)

This data frame contains information about Managers (Name, Nav and Date) which are updatede every day with new values for the current date and maybe old values could be updated too in case of errors.

How can I accomplish this task in R?

I treid to use sqlUpdate but it returns me the following error:

> sqlUpdate(channel, dbdata, tablename = "ManagerNav")
Error in sqlUpdate(channel, dbdata, tablename = "ManagerNav") : 
  cannot update ‘ManagerNav’ without unique column

Wilone

Does your table contain a primary key? I guess that is what the error refers to. Maybe you could add an Index column to the table with auto_increment, and try again. – Aurelia

The primary key should be Manager Name and Nav... – Wilone

Mh, in that case, maybe sqlUpdata / sqlSavedoes not support tables that have a two column primary key?! Or you have to specify it somehow different? Just a guess, because for me the error sounds like it has a problem with the PK. I just wonder, if your sqlSave command sets the PK as you want it to be. Maybe you could still check this in your database if it is correctly set. – Aurelia

Answer 1

When you create a table "the white shark-way" (see documentation), it does not get a primary index, but is just plain columns, and often of the wrong type. Usually, I use your approach to get the columns names right, but after that you should go into your database and assign a primary index, correct column widths and types.

After that, sqlUpdate() might work; I say might, because I have given up using sqlUpdate(), there are too many caveats, and use sqlQuery(..., paste("Update....))) for the real work.

Eady

How to use sqlQuery(...,paste("Update table SET...)? I mean the data I want to update is a 3 columns data frame... – Wilone

Well, usually I also have multiple lines. Use a loop or whatever to iterate through all lines. It is practicable because sqlUpdate internally uses the same method. Simply type sqlUpdate, and you see the loop in the lower part. – Eady

Answer 2

What I would do for this is the following

Solution 1

sqlUpdate(channel, dbdata,tablename="ManagerNav", index=c("ManagerNav"))

Solution 2

Lcolumns <- list(dbdata[0,]) 
sqlUpdate(channel, dbdata,tablename="ManagerNav", index=c(Lcolumns))

Index is used to specify what columns R is going to update. Hope this helps!

Odont

Answer 3

If none of the other solutions work and your data is not that big, I'd suggest using sqlQuery() and loop through your dataframe.

one_row_of_your_df <- function(i) {
  sql_query <- 
paste0("INSERT INTO your_table_name (column_name1, column_name2, column_name3) VALUES",
"(",
"'",your_dataframe[i,1],",",
"'",your_dataframe[i,2],"'",",",
"'",your_dataframe[i,3],"'",",",
")"
  )
  return(sql_query)
}

This function is Exasol specific, it is pretty similar to MySQL, but not identical, so small changes could be necessary.

Then use a simple for loop like this one:

 for(i in 1:nrow(your_dataframe)) 
    {
        sqlQuery(your_connection, one_row_of_your_df(i))
    }

Argo