R:替换一个表中的值，基于另一个表

library(DBI)
library(dplyr)

# 建立与数据库的连接
con <- dbConnect(RSQLite::SQLite(), dbname = "database.db")

# 创建两个示例表
dbExecute(con, "CREATE TABLE table1 (id INT, value VARCHAR(255))")
dbExecute(con, "CREATE TABLE table2 (id INT, new_value VARCHAR(255))")

# 向表1中插入数据
dbExecute(con, "INSERT INTO table1 VALUES (1, 'old_value')")
dbExecute(con, "INSERT INTO table1 VALUES (2, 'old_value')")
dbExecute(con, "INSERT INTO table1 VALUES (3, 'old_value')")

# 向表2中插入数据
dbExecute(con, "INSERT INTO table2 VALUES (1, 'new_value1')")
dbExecute(con, "INSERT INTO table2 VALUES (2, 'new_value2')")

# 使用SQL语句进行表的连接和更新
dbExecute(con, "UPDATE table1 SET value = table2.new_value FROM table2 WHERE table1.id = table2.id")

# 查询更新后的表1
result <- dbGetQuery(con, "SELECT * FROM table1")
print(result)

# 关闭数据库连接
dbDisconnect(con)

# 创建两个示例数据框
table1 <- data.frame(id = c(1, 2, 3), value = c('old_value', 'old_value', 'old_value'))
table2 <- data.frame(id = c(1, 2), new_value = c('new_value1', 'new_value2'))

# 使用merge()函数进行表的连接
merged_table <- merge(table1, table2, by = 'id', all.x = TRUE)

# 使用mutate()函数更新表中的值
updated_table <- merged_table %>% mutate(value = ifelse(!is.na(new_value), new_value, value))

# 打印更新后的表
print(updated_table)