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

Question

I need to update the conditionnaly column values ​​in other columns in some table table PostgreSQL. I managed to do this by writing an SQL statement in R and executing it using a package dbExecutefrom DBI.

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 this possible using only syntax dplyr? 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 statement select. copy_toallows only for parameters appendand overwitetherefore I can’t understand how to use it if not deleted, then add filtered observations ...

Answer 1

dplyr 0.7.1 ( dbplyr 1.1.0) , , . UPDATE dbExecute() .

:

• copy_to().
• .
• a DELETE FROM ... WHERE id IN (SELECT id FROM <temporary table>)
• INSERT INTO ... SELECT * FROM <temporary table>

INSERT INTO ... ON CONFLICT DO UPDATE DELETE, INSERT.