如何将SQLite数据库中的data.frame与R环境中的data.frame关联更新？

需求说明 #

SQLite数据库中有一张df表，包含code、var2、var3列，其中var3为缺失值。需要用仅存在于R全局环境的df2（仅含code和var3列，存储对应code的有效值）关联更新df表的var3列，最终将更新结果同步回SQLite数据库。

初始代码场景 #

library(RSQLite)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

# 初始化SQLite中的df表
df <- data.frame(
  code= c("A", "B"),
  var2 = c(10, 5),
  var3 = c(NA, NA)
)
DBI::dbWriteTable(con, "df", df)

# R全局环境中的匹配数据
df2 <- data.frame(
  code= c("A", "B"),
  var3 = c(8, 6)
)

预期更新后效果 #

数据库中的df表数据变为：

df <- data.frame(
  code= c("A", "B"),
  var2 = c(10, 5),
  var3 = c(8, 6)
)

解决方案 #

方法1：读表合并后写回（适合小数据集） #

把数据库中的df表读入R环境，和df2按code关联更新，再覆盖写回数据库：

# 读取数据库中的df表
db_df <- DBI::dbReadTable(con, "df")

# 按code关联，更新var3列
updated_df <- merge(db_df, df2, by = "code", all.x = TRUE)
updated_df$var3 <- updated_df$var3.y
updated_df <- updated_df[, c("code", "var2", "var3")]

# 覆盖写回数据库
DBI::dbWriteTable(con, "df", updated_df, overwrite = TRUE)

# 验证结果
DBI::dbReadTable(con, "df")

方法2：用临时表+SQL更新（适合大数据集） #

无需全量读取表，将df2导入SQLite临时表，直接执行SQL更新语句，效率更高：

# 将df2导入SQLite临时表
DBI::dbWriteTable(con, "df2_temp", df2, temporary = TRUE)

# 执行SQL更新语句，通过code关联更新var3
DBI::dbExecute(con, "
  UPDATE df
  SET var3 = (SELECT var3 FROM df2_temp WHERE df.code = df2_temp.code)
  WHERE EXISTS (SELECT 1 FROM df2_temp WHERE df.code = df2_temp.code)
")

# 验证结果
DBI::dbReadTable(con, "df")

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