title: Bulk-Update an SQLite column in R using RSQLite and bind.data
body: <p>I am using R in combination with SQLite using RSQLite to persistate my data since I did not have sufficient RAM to constantly store all columns and calculate using them. I have added an empty column to the SQLite database using:</p>
  
  <pre><code>dbGetQuery(db, "alter table test_table add column newcol real)
  </code></pre>
  
  <p>Now I want to fill this column using data I calculated in R and which is stored in my data.table column dtab$newcol. I have tried the following approach:</p>
  
  <pre><code>dbGetQuery(db, "update test_table set newcol = ? where id = ?", bind.data = data.frame(transactions$sum_year, transactions$id))
  </code></pre>
  
  <p>Unfortunately, R seems like it is doing something but is not using any CPU time or RAM allocation. The database does not change size and even after 24 hours nothing has changed. Therefore, I assume it has crashed - without any output.</p>
  
  <p>Am I using the update statement wrong? Is there an alternative way of doing this?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I have also tried the RSQLite functions dbSendQuery and dbGetPreparedQuery - both with the same result. However, what does work is updating a single row without the use of bind.data. A loop to update the column, therefore, seems possible but I will have to evaluate the performance since the dataset is huge.</p>