0

Does anyone know how I can use RMySQL (or another library) to update a row in a table, rather than having to pull out the full table and push it back in? I don't want to read such a huge table into memory just to update one row.

What I am trying to do is pull out a row, change some of the values in there within R and push the same row object back into the table.

However, dbWriteTable seems to replace the entire table rather than just the row I specify.

Cyrus Mohammadian
  • 4,982
  • 6
  • 33
  • 62
Noobnoob
  • 27
  • 8

2 Answers2

3

The easiest way is to construct a string within R containing the adequate SQL Update statement and use dbSendQuery to push your data back into the table.

Constantine
  • 479
  • 1
  • 9
  • 19
0

Using sqldf package:

library(sqldf)
table_name = data.frame(a = 1:10, b = 4)
# Open connection
sqldf()
fn$sqldf("update table_name set b=1")
ans = sqldf("select * from main.table_name")
# Close connection
sqldf()
print(table_name)
Kartheek Palepu
  • 972
  • 8
  • 29
  • I don't really understand what you've done here. Can you break it down? What kind of object is is fn? – Noobnoob Nov 02 '15 at 11:02
  • In general we must use the fn$ prefix to invoke the interpolation functionality. i.e. We can pass the R- objects or variables into the sql query. `fn$sqldf("update table_name set b=$var")` Where var = 1 in the above code – Kartheek Palepu Nov 03 '15 at 06:41