R: Update a mysql table with data frame

Question

I have a MariaDB and I want to update a table with a local R data frame. As an example, I have a table with these column names:

id,foo,bar

id is the primary key on the data base table.

Is there a function with which I can easily update the remote data base with my local data frame? Ideally, I am looking for something like this:

update_table(con, remote_table_name, local_data_frame, primary_key="id")

I am aware that I could write the SQL statement like this into a string and then execute it with DBI::dbSendStatement(con, query):

INSERT INTO mytable (id, foo, bar)
VALUES (1, 'a1', 'b1'),
(2, 'a2', 'b2'),
(3, 'a3', 'b3'),
(4, 'a4', 'b4'),
ON DUPLICATE KEY UPDATE id=VALUES(id),
foo=VALUES(foo),
bar=VALUES(bar)

But I would be surprised if there is not an out-of-the-box solution for this seemingly simple operation. Does anybody have an idea if such a function is embedded in a package?

Answer 1

The dbx package has a function which does exactly what I needed:

dbxUpdate(db, table, records, where_cols=c("id"))

It does work with MariaDB.

Answer 2

Now, the dbplyr package also has rows_update()

rows_update(table, records, by = "id", copy = TRUE)