My database table looks approximately like this:
+-----+-------+--------------------+-----------+----------+
| ID1 | ID2 | FilePath1 | FilePath2 | Status |
+-----+-------+--------------------+-----------+----------+
| 1 | Test1 | MyFolder\R\Folder1 | NULL | Open |
| 2 | Test2 | MyFolder\R\Folder2 | NULL | Open |
| 3 | Test3 | MyFolder\R\Folder3 | NULL | Finished |
| 4 | Test4 | MyFolder\R\Folder4 | NULL | Finished |
+-----+-------+--------------------+-----------+----------+
The first column (ID1) is defined as PK. However, ID2 is also unique.
Now I'd like to be able to change FilePath2 and Status with R using sqlUpdate() from RODBC package. So I try the following:
db.df <- data.frame(ID1=1, ID2='Test1',
FilePath2='MyFolder\R\Folder5', Status='Finished',
stringsAsFactors=FALSE)
sqlUpdate(myconn, db.df, tablename='mytable', index='ID2', verbose=TRUE)
wherein db.df is a data frame with one row and column names corresponding to those within the database table (however, I leave out some of the columns, in this case FilePath1, and I'd prefer to leave out ID1, too, if possible). My aim is to get the following:
+-----+-------+--------------------+--------------------+----------+
| ID1 | ID2 | FilePath1 | FilePath2 | Status |
+-----+-------+--------------------+--------------------+----------+
| 1 | Test1 | MyFolder\R\Folder1 | MyFolder\R\Folder5 | Finished |
| 2 | Test2 | MyFolder\R\Folder2 | NULL | Open |
| 3 | Test3 | MyFolder\R\Folder3 | NULL | Finished |
| 4 | Test4 | MyFolder\R\Folder4 | NULL | Finished |
+-----+-------+--------------------+--------------------+----------+
I get the folllowing error:
Error in sqlUpdate(myconn, db.df, tablename = 'mytable', index = 'ID2', :
index column(s) ID2 not in database table
What might be the reason for this problem?
EDIT: I've bypassed the problem by sending a direct SQL Query:
out.path <- 'MyFolder\\\\R\\\\Folder5'
update.query <- paste("UPDATE mytable ",
"SET FilePath2='", out.path, "', Status='Finished' ",
"WHERE ID2='Test1'", sep="")
dummy <- sqlQuery(myconn, update.query)
Although this might not be a neat way, it does what it should do. However, I still don't understand what's the matter with sqlUpdate, so I hope someone can shed light on it.