4

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?

MKR
  • 1,620
  • 7
  • 20
  • 3
    The [`dbx`](https://github.com/ankane/dbx) package supports upserts, not sure of its status with mariadb. Your query is only bespoke because you hand-jam the data; the way I do it is to `dbWriteTable` the data to be updated into a temp table on the database, then use the `INSERT ... ON DUPLICATE ...` query from the temp table to the table to be updated. It's far more generalizable that way, and benefits from other existing `DBI` functions. – r2evans Apr 21 '22 at 16:22
  • 1
    The `dbx` package does exactly what I needed. Thanks for pointing me to the right direction. – MKR Apr 22 '22 at 07:07

2 Answers2

3

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.

MKR
  • 1,620
  • 7
  • 20
1

Now, the dbplyr package also has rows_update()

rows_update(table, records, by = "id", copy = TRUE)
Maximilian Girlich
  • 306
  • 1
  • 2