CODEWITHSUNDEEP
rrmysql
Rosa
Rosa

Reputation: 1833

How to update MySQL table using RMySQL package

Instead of append or overwrite an entire table to database, is there any function in RMySQL that can update only part of the table? Because some of our data could be imcomplete someday, that I need to remain the old table and only replace the rows that has new data coming in, here is the function I wrote, but did not work, any help would be appreciated:

col.info <- "(id int, timestamp bigint, yyyy int, mm int, dd int, value double, 
PRIMARY KEY(id, timestamp, yyyy, mm, dd))"

Func <- function(con, tbl.name, dat.set, col.info) {
  if (dbExistsTable(con, tbl.name)) {  
  dbWriteTable(con, tbl.name, dat.set, row.names=F, append=T);  #what can I change the append for??

  } else { 
  dbSendQuery(con, paste("CREATE TABLE IF NOT EXISTS", tbl.name, col.info, sep=" ")); 
  dbWriteTable(con, tbl.name, dat.set, row.names=F, append=T);
  }
} 

Func(conn_table, "daily_update", df, col.info)

Upvotes: 2

Views: 5220

Answers (1)

RDRR
RDRR

Reputation: 880

I recently came across the dbx package which does exactly what you need.

The following code will execute an upsert (on duplicate key update) query on your database table. Just replace the where_cols argument with whichever columns act as your primary key.

install.packages("dbx")
library(dbx)
dbx::dbxUpsert(con, tbl.name, dat.set, where_cols = c("id"))

Upvotes: 3

Related Questions