我可以在R中仅使用dplyr语法运行SQL更新语句吗 [英] Can I run an SQL update statement using only dplyr syntax in R

问题描述

我需要有条件地更新某些PostgreSQL数据库表中其他列的列值.我设法在R中编写了一条SQL语句，并使用DBI包中的dbExecute执行了它.

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)

是否可以仅使用dplyr语法来做到这一点?我出于好奇而尝试了

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)

在R中工作，但显然在db中什么也不做，因为它使用select语句. copy_to仅允许使用append和overwite选项，因此除非删除然后附加过滤后的观测值，否则我看不到如何使用它.

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

推荐答案

当前dplyr 0.7.1(使用dbplyr 1.1.0)不支持此功能，因为它假定所有数据源都是不可变的.通过dbExecute()发行UPDATE似乎是最好的选择.

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. 通过copy_to()将数据帧写入数据库中的临时表.
2. 开始交易.
3. 发出DELETE FROM ... WHERE id IN (SELECT id FROM <temporary table>)
4. 发出INSERT INTO ... SELECT * FROM <temporary table>
5. 提交交易

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

根据您的架构，您可能可以做一个INSERT INTO ... ON CONFLICT DO UPDATE而不是DELETE然后是INSERT.

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

这篇关于我可以在R中仅使用dplyr语法运行SQL更新语句吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！