R sqlite: update with two tables gives syntax error " near ".":

Question

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"))

The UPDATE is:

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

I get the following error:

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.

agstudy · Answer 1 · 2013-01-11T10:33:18.930

2

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

I would use RSQLite to this.

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 
       )

Then you can do this :

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

Edit

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

edited Jan 11 '13 at 10:33

answered Jan 11 '13 at 08:34

agstudy

119,832
17
199
261

1

Tip: sometimes the best way to find SQL syntax errors is to learn to read the syntax diagrams: http://www.sqlite.org/syntaxdiagrams.html#update-stmt - the SET after an UPDATE takes a column name and not a table.column name. – Spacedman Jan 11 '13 at 08:44
@Spacedman Thanks for the tip. But why are u saying it to me , is there any problem with my answer?:) – agstudy Jan 11 '13 at 08:48
@Spacedman no it is ok. good tip. it is always good to review the basics. – agstudy Jan 11 '13 at 08:56
1

@agstudy. Updates with library(sqldf) are possible. I show it in my solution below. Thanks for your answer. I learned another approach. Sorry about my errors. I forget to insert the row FROM x2. I'm not sure if your suggestion using FROM x1,x2 is correct. – giordano Jan 11 '13 at 10:06
@giordano read the help and specially the part The typical action of sqldf is to.... Whatever you use sqldf or Rsqlite the problem was a syntax problem. did you test it ? – agstudy Jan 11 '13 at 10:14
@agstudy: yes. I tested the code. The error you are marked as second should be corrected to "FROM x2". Otherwise you will get h=1 in each row. This makes sense since the results of "SELECT x1.id FROM x1,x2 WHERE x1.id = x2.id AND x1.t < x2.start" gives 1, which is always true. There were 3 syntax problem. The one mentioned before, the one that for SET it is obviously not allowed to use table.column. These are both sql-syntex issues. Than the third is not a sql-syntax issue but one of the sqld library. After UPDATE the result should be returned by SELECT * FROM main.x1 (see solution below). – giordano Jan 11 '13 at 10:27
@giordano yes for `FROM x1,x2` ( you forget the from part so I add a one) I will correct my answer to fom x1, but really I recommand you using Rsqlite, it is the way for such manipulations. – agstudy Jan 11 '13 at 10:32
@agstudy: Thanks. I agree with you regarding "such manipulation". Usually I do data management on a MySQL database and analysis in R (retrieving data from R with ODBC). sqldf I usually use for summary statistics of data frames and not for data manipulation. Question: I suppose Rsqlite is the interface for an external sqlite engine and not for the one in R, isn't it? – giordano Jan 11 '13 at 11:12
@giordano Rsqlite is exteranl. It is like csv files but with read/write operations in sql. In you case I would use RODBS:RMysQL to read/savs tables. For summaries it is better to use R (plyr ,reshape2,..). – agstudy Jan 11 '13 at 11:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22587/discussion-between-giordano-and-agstudy) – giordano Jan 11 '13 at 13:07

score 1 · Answer 2 · edited Jan 12 '13 at 01:11

I found this solution:

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"))

Giving:

> x1

  id          t h
1  1 2000-01-01 1
2  1 2000-01-15 0
3  1 2000-01-31 0

Source: https://code.google.com/p/sqldf/#8._Why_am_I_having_problems_with_update? Other things to remind: obviously alias do not work, for example UPDATE x1 a ... Thanks for help.

score 0 · Answer 3 · answered Jan 11 '13 at 08:34

0

I think your inner SELECT doesn't say where its selecting from. Try it separately. I thought it should look more like:

SELECT * FROM x1,x2 WHERE x1.id = x2.id AND x1.t < x2.start

answered Jan 11 '13 at 08:34

Spacedman

92,590
12
140
224

R sqlite: update with two tables gives syntax error " near ".":

3 Answers3

Linked