sqldf update error

Question

I have two Datasets, something like

Datset1

Index  Mac Date         Cons
1      35  1432425600   Nil
2      35  1431993600   Nil
3      35  1433894400   Nil
4      35  1433376000   Nil

 Dataset2

Index  Mac Start_Date  End_Date       Cons
1      35  1432339200  1432771200     D1
2      35  1433116800  1435017600     D2

So, All I am looking for is a resultant set, from Dataset1 which Match on Mac column on dataset2 and [Dataset1.Date] should be between the [Dataset2.Start_Date] & [Dataset.End_Date] , to get the column Cons. So Ideally My result should look something like

Final Dataset
Index  Mac Date         Cons
1      35  1432425600   D1
2      35  1431993600   Nil
3      35  1433894400   D2
4      35  1433376000   D2

I am trying to achieve it in R while using sqldf, To begin I used 1 simple code on Mac only

sqldf2 <- sqldf("update dataset1
            set dataset1.Cons = dataset2.Cons
            FROM dataset1 INNER JOIN dataset2
            ON dataset1.Mac = dataset2.Mac")

but it gave me an error

Error in rsqlite_send_query(conn@ptr, statement) : near ".": syntax error

For further I am planning to use something like this, which is definitely not right, please help with that too

sqldf2 <- sqldf("update dataset1
            set dataset1.Cons = dataset2.Cons
            FROM dataset1 INNER JOIN dataset2
            ON dataset1.Mac = dataset2.Mac 
            AND (dataset1.Date > dataset2.Start_Date & dataset1.Date<dataset2.End_Date)")

I appreciate all the help!

score 2 · Accepted Answer · answered Dec 20 '16 at 08:36

We can use left join:

# dummy data*
dataset1 <- read.table(text = "
rn  Mac Date         Cons
1      35  1432425600   Nil
2      35  1431993600   Nil
3      35  1433894400   Nil
4      35  1433376000   Nil", header = TRUE)

dataset2 <- read.table(text = "
rn  Mac Start_Date  End_Date       Cons
1      35  1432339200  1432771200     D1
2      35  1433116800  1435017600     D2", header = TRUE)

library(sqldf)

sqldf("select a.rn, a.Mac, a.Date, b.Cons
       from dataset1 a
       left join dataset2 b
       on a.Mac = b.Mac and
          a.Date >= b.Start_Date and
          a.Date <= b.End_Date")

#   rn Mac       Date Cons
# 1  1  35 1432425600   D1
# 2  2  35 1431993600 <NA>
# 3  3  35 1433894400   D2
# 4  4  35 1433376000   D2

Note: I changed "Index" to "rn", otherwise this throws an error.

Thanks, it is helpful. Please also direct what mistake I made while using update in sqldf, as I shared the code. — Manu Sharma, Dec 20 '16 at 08:55
@manusharma See [this post](http://stackoverflow.com/questions/773441): "SQLite doesn't support JOINs in UPDATE statements" — zx8754, Dec 20 '16 at 08:59

sqldf update error

1 Answers1