How do I run a SQL update statement in RODBC?

Question

When trying to run an update with a SQL statement with the sqlQuery function in RODBC, it brings up an error

"[RODBC] ERROR: Could not SQLExecDirect '.

How do you run a direct update statement with R?

Answer 1

You cannot use a plain SQL update statement with the SQL query function, it just needs to return a resultset. For example, the following statement won't work:

sql="update mytable set column=value where column=value"
cn <-odbcDriverConnect(connection="yourconnectionstring")
resultset <- sqlQuery(cn,sql)

But if you add an output statement, the SQL query function will work fine. For example.

sql="update mytable set column=value output inserted.column where column=value"
cn <-odbcDriverConnect(connection="yourconnectionstring")
resultset <- sqlQuery(cn,sql)

I just added a function to make it easy to take your raw sql and quickly turn it into an update statement.

  setUpdateSql <-function(updatesql, wheresql, output="inserted.*"){
   sql=paste(updatesql," output ",output, wheresql)
   sql=gsub("\n"," ",sql) #remove new lines if they appear in sql
   return(sql)

}

So now I just need to split the SQL statement and it will run. I could also add an "inserted.columnname" if I didn't want to return the whole thing.

sql=setUpdateSql("update mytable set column=value","where column=value","inserted.column")#last parameter is optional
cn <-odbcDriverConnect(connection="yourconnectionstring")
resultset <- sqlQuery(cn,sql)

The other advantage with this method is you can find out what has changed in the resultset.