我想做一个更新与两个表在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)
有人知道出了什么问题吗?谢谢你的帮助。
为什么使用sqldf
进行更新?我认为sqldf
只适用于select
语句。
我将使用RSQLite
。
首先我纠正你的sql语句。我更喜欢使用sep 'n',用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
)
我找到了这个解决方案:
x1$h <- 0
x1 <- sqldf(c("UPDATE x1
SET h = 1
WHERE EXISTS (SELECT x1.id
FROM x2
WHERE x1.id = x2.id
AND x1.t < x2.start
)",
"SELECT * FROM main.x1"))
给:> x1
id t h
1 1 2000-01-01 1
2 1 2000-01-15 0
3 1 2000-01-31 0
来源:https://code.google.com/p/sqldf/8. _why_am_i_having_problems_with_update ?其他需要提醒的事情:显然别名不起作用,例如UPDATE x1 a…谢谢你的帮助。
我认为你的内部SELECT没有说它从哪里选择。单独试试吧。我认为它应该看起来更像:
SELECT * FROM x1,x2 WHERE x1.id = x2.id AND x1.t < x2.start