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

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. — Daniel Fischer, Apr 23 '13 at 09:37
Mh, in that case, maybe `sqlUpdata / sqlSave`does 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. — Daniel Fischer, Apr 23 '13 at 11:30

score 8 · Answer 1 · edited Oct 10 '18 at 04:58

8

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.

edited Oct 10 '18 at 04:58

Mr.Takeo

223
3
17

answered Apr 23 '13 at 09:40

Dieter Menne

10,076
44
67

1

How to use sqlQuery(...,paste("Update table SET...)? I mean the data I want to update is a 3 columns data frame... – Lorenzo Rigamonti Apr 23 '13 at 11:02
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. – Dieter Menne Apr 23 '13 at 13:10

score 3 · Answer 2 · edited Oct 10 '18 at 03:53

3

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!

edited Oct 10 '18 at 03:53

Mr.Takeo

223
3
17

answered Nov 25 '14 at 16:52

Nedinator

1,052
13
24

score 0 · Answer 3 · edited Feb 15 '22 at 20:21

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))
    }

update an SQL table via R sqlSave

3 Answers3