在R中更新SQLite数据库表：dbplyr及SQL语句问题排查

一、让compute()覆盖现有表 #

dbplyr的compute()默认不允许覆盖已存在的表，可通过以下两种方式解决：

方法1：使用overwrite=TRUE参数（推荐，适用于dbplyr 2.0+） #

直接在compute()中添加overwrite=TRUE参数，即可覆盖目标表：

left_join(x = tbl(con, "df1"),
          y = tbl(con, "airlines"),
          by = "carrier") |> 
  show_query() |> 
  compute(name = "df1", temporary = F, overwrite = TRUE)

方法2：手动删除表后重新生成 #

若你的dbplyr版本不支持overwrite参数，可先删除现有表再执行连接：

# 删除已存在的df1表
dbRemoveTable(con, "df1")

# 重新执行连接并生成表
left_join(x = tbl(con, "flights"),
          y = tbl(con, "planes"),
          by = c("tailnum", "year")) |> 
  show_query() |> 
  compute(name = "df1", temporary = F)

二、修复SQLite的UPDATE语句错误 #

你之前的SQL语句采用了PostgreSQL风格的UPDATE ... FROM语法，但SQLite不支持该写法。根据SQLite版本，可使用以下两种正确写法：

写法1：子查询匹配（兼容所有SQLite版本） #

UPDATE df1
SET name = (
  SELECT airlines.name
  FROM airlines
  WHERE airlines.carrier = df1.carrier
)
WHERE EXISTS (
  SELECT 1
  FROM airlines
  WHERE airlines.carrier = df1.carrier
);

写法2：UPDATE JOIN（仅支持SQLite 3.33.0+） #

如果你的SQLite版本在3.33.0及以上，可使用更简洁的JOIN语法：

UPDATE df1
SET name = airlines.name
FROM airlines
WHERE df1.carrier = airlines.carrier;

执行语句时直接调用dbExecute即可：

query <- "
  UPDATE df1
  SET name = airlines.name
  FROM airlines
  WHERE df1.carrier = airlines.carrier
"
dbExecute(con, query)

内容的提问来源于stack exchange，提问作者Hugo