R sqlite:更新两个表会出现语法错误在“."附近: [英] R sqlite: update with two tables gives syntax error " near ".":

问题描述

我想对 sqlite 上的两个表进行更新.

I would like to do a UPDATE with two tables on sqlite.

x1 <- data.frame(id = rep(1,3),
                  t = as.Date(c("2000-01-01","2000-01-15","2000-01-31"))
)
x1.h <- 0
x2 <- data.frame(id = 1, start = as.Date("2000-01-14"))

更新是:

sqldf(paste("UPDATE x1"
        ," SET x1.h = 1"
        ," WHERE EXISTS (SELECT *"
        ,"               FROM x2"
        ,"               WHERE x1.id = x2.id"
        ,"                     AND x1.t < x2.start"
        ,"               )"
        )
  )

我收到以下错误:

Error in sqliteExecStatement(con, statement, bind.data) : 
   RS-DBI driver: (error in statement: near ".": syntax error)

有人知道出了什么问题吗?感谢您的帮助.

Has someone an idea what goes wrong? Thanks for helps.

推荐答案

你为什么要使用 sqldf 来更新?我认为 sqldf 仅适用于 select 语句.

Why are u using sqldf to update? I think sqldf is ony for select statement.

我会使用 RSQLite 来做到这一点.

I would use RSQLite to this.

首先我更正你的sql语句.我更喜欢使用 sep '\n' ，用 cat

First I correct your sql statetemnt. I prefer use sep '\n' , to get pretty request with cat

str.update <- paste(" UPDATE x1"
            ," SET h = 1 "              ## here the error
            ," WHERE EXISTS (SELECT * "
             ,"              FROM x2 "             ## here second error 
             ,"              WHERE x1.id = x2.id "
            ,"               AND x1.t < x2.start "
            ,"       )"
      ,sep ='\n'
)


cat(str.update)
 UPDATE x1
 SET h = 1 
 WHERE EXISTS (SELECT * 
              FROM x1,x2    ##
              WHERE x1.id = x2.id 
               AND x1.t < x2.start 
       )

然后你可以这样做:

library(RSQLite)
con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "x1", x1)            # I create my table x1
dbWriteTable(con, "x2", x2)            # I create my table x2
res <- dbSendQuery(con, str.update)   
dbReadTable(con,name='x1')            ## to see the result

编辑

我在 Op 澄清后编辑我的答案(FROM x1,x2 变成 FROM x2)

I edit my answer after Op clarifications (FROM x1,x2 becomes FROM x2)

这篇关于R sqlite:更新两个表会出现语法错误在“."附近:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！