Can I run an SQL update statement using only dplyr syntax in R

Question

I need to update column values conditionnaly on other columns in some PostgreSQL database table. I managed to do it writing an SQL statement in R and executing it with dbExecute from DBI package.

library(dplyr)
library(DBI)

# Establish connection with database
con <- dbConnect(RPostgreSQL::PostgreSQL(), dbname = "myDb",
                 host="localhost", port= 5432, user="me",password = myPwd)

# Write SQL update statement
request <- paste("UPDATE table_to_update",
                 "SET var_to_change = 'new value' ",
                 "WHERE filter_var = 'filter' ")

# Back-end execution
con %>% dbExecute(request)

Is it possible to do so using only dplyr syntax ? I tried, out of curiosity,

con %>% tbl("table_to_update") %>%
   mutate(var_to_change = if (filter_var == 'filter') 'new value' else var_to_change)

which works in R but obviously does nothing in db since it uses a select statement. copy_to allows only for append and overwite options, so I can't see how to use it unless deleting then appending the filtered observations...

Williamswilliamsburg

Answer 1

Current dplyr 0.7.1 (with dbplyr 1.1.0) doesn't support this, because it assumes that all data sources are immutable. Issuing an UPDATE via dbExecute() seems to be the best bet.

For replacing a larger chunk in a table, you could also:

1. Write the data frame to a temporary table in the database via copy_to().
2. Start a transaction.
3. Issue a DELETE FROM ... WHERE id IN (SELECT id FROM <temporary table>)
4. Issue an INSERT INTO ... SELECT * FROM <temporary table>
5. Commit the transaction

Depending on your schema, you might be able to do a single INSERT INTO ... ON CONFLICT DO UPDATE instead of DELETE and then INSERT.

Oden

How about using REPLACE INTO instead of INSERT INTO? And in this way, we can ignore the third step of DELETE relevant rows from the table. – Selves

I think the syntax might be slightly different in PostgreSQL, but I'm not sure. For other databases this might work. – Oden

Yes, you are right. I have tested to use REPLACE INTO in SQLite, which works well. Thanks for your comment. @Oden – Selves