Updating multiple SQL records with DBI dbBind

[brad_schrage]

I have R Studio Workbench which communicates with MSSQL databases (which have been properly mounted). I can insert records using DBI using dbAppendTable. In the past, to UPDATE multiple records in MSSQL using R, I could write a recipe that used DBI::dbSendQuery and DBI::dbBind. This does not seem to work any longer. A dbSendQuery without parameters will work fine when not using dbBind, it's when it becomes parameterized that it no longer binds. Please help!

As a toy example, I created a two-column table. I want to update column A_Number WHERE ID = 2.

I want to update the value from 324 --> 15.15:

1. Collect the data
   tbl(PM_MASTER, 'test_table_update') %>% collect()

     ID A_Number
     1     1.25
     2   324.

2. df_test is a one row tibble that I want to bind to and update my test_table_update SQL table:

df_test <- tbl(PM_MASTER, 'test_table_update') %>% 
    filter(ID == 2) %>% 
    collect() %>% 
    mutate(A_Number = 15.15)

df_test

 ID A_Number
 2     15.2

3. Attempt the update: No errors show here but ID 2 did not update to 15.15 as expected

update <- dbSendQuery(PM_MASTER, 'UPDATE test_table_update SET "A_Number"=?  WHERE ID=?')
dbBind(update, df_test)
dbClearResult(update)
tbl(PM_MASTER, 'test_table_update') %>% collect()

     ID A_Number
      1     1.25
      2   324.

Thanks,
Brad

[brad_schrage]

I'll answer my own question. It seems as though you have to bind in order. This is strange, but in my example if I relocate "A_Number" i.e. as the first field, then ID can be used in the where clause. Seems odd that key/value pairs don't align, but I guess this way works.