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?
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.