mysql - 通过 R 更新 MySql 表的问题

Question

给定的代码有什么问题..

dbExecute(con_pratham,paste("Update unit_dummy  set isDeleted=",paste(data_to_update$IsDeleted)," , status =", paste0(data_to_update$status), "where UnitId =",paste(data_to_update$UnitId)))

它应该根据其 UnitId 更新两列 “status” 和 IsDeleted。这里“data_to_update”是具有 500 个观察值的数据帧。这些行中的每一行都有“Status”和“IsDeleted”以及“UnitId”。当我打印上面的代码时，它完美地生成了大约 500 个与此类似的更新 SQL 语法..

[476] "Update unit_dummy  set isDeleted= 0  , status = 1 where UnitId = 56061"
[477] "Update unit_dummy  set isDeleted= 0  , status = 1 where UnitId = 56063"
[478] "Update unit_dummy  set isDeleted= 0  , status = 1 where UnitId = 56065"
[479] "Update unit_dummy  set isDeleted= 1  , status = 0 where UnitId = 56069"
[480] "Update unit_dummy  set isDeleted= 1  , status = 0 where UnitId = 56070"
[481] "Update unit_dummy  set isDeleted= 1  , status = 1 where UnitId = 56095"
[482] "Update unit_dummy  set isDeleted= 0  , status = 1 where UnitId = 56100"
[483] "Update unit_dummy  set isDeleted= 1  , status = 0 where UnitId = 56132"

当我复制任何这些 sql 行并在服务器上运行时，它工作正常，但不能通过 R。请指导我这一点..

Answer 1

我会尝试这样的事情:

sqls <- paste("Update unit_dummy  set isDeleted=",paste(data_to_update$IsDeleted)," , status =", 
  paste0(data_to_update$status), "where UnitId =",paste(data_to_update$UnitId))


for (sql in sqls){
    dbExecute(con_pratham, sql)
}