4

I have a problem with SQLdf. Although I am trying to update a table, it always gives NULL as an output. I red things about this problem but I cannot figure out how to solve it. My code is:

fn$sqldf("update cons set V1='%$numbernew%' where V1=$'contact'")

But after I check it to see if something has changed, all are the same as in the beginning. Any ideas would help.

Tony
  • 469
  • 1
  • 6
  • 18
  • 2
    Scanning the FAQs for the package can sometimes be [useful](https://code.google.com/p/sqldf/#8._Why_am_I_having_problems_with_update?). – joran Nov 21 '13 at 19:54
  • I know but its not about the arguments. Its a problem that occurs sometimes and it has something to do with sqldf package, that does not let you update a table easily... – Tony Nov 21 '13 at 19:55

1 Answers1

8

As Joran mentioned in a comment this question is an sqldf FAQ. In fact its sqldf FAQ #8.

As discussed there the problem is that you asked to update the table but never asked it to return the table. Also $'contract' should be '$contract' since you want to substitute in contract and then surround that substitution with single quotes.

# set up test data for reproduciblity
con <- data.frame(V1 = c("a", "b", "c"))
contract <- "a"
numbernew <- "x"

Now that we have some data try this:

sql1 <- fn$identity("update con set V1 ='%$numbernew%' where V1 = '$contract' ")
sql2 <- "select * from main.con"
sqldf(c(sql1, sql2))

The result is:

   V1
1 %x%
2   b
3   c

This would work too:

sqldf() # start a sequence of SQL statements

fn$sqldf("update con set V1 ='%$numbernew%' where V1 = '$contract' ")
ans <- sqldf("select * from main.con")

sqldf() # SQL statements finished
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • Hey, thank you for the answer. I did have the '$contact' correct. I just edited it wrong here. My problem (after some time of searching) is that the variable numbernew contains symbols like ; : etc that might be recognized by sql as commands. ANy idea how to deal with these? – Tony Nov 22 '13 at 10:35
  • String literals may contain special characters. If they contain single quotes then double them, e.g. `'Joe''s Pizza'` – G. Grothendieck Nov 22 '13 at 12:14
  • What about ? and . ?? – Tony Nov 22 '13 at 12:26
  • They should both work. Try: `sqldf("select '?', '??', 'Joe''s Pizza' ")` – G. Grothendieck Nov 22 '13 at 12:29
  • In general, my function reads lines from a .vcf file (phone contacts). All I want to do is to use SQL expressions to export something from the list etc. But as it can be seen from a .vcf file the phone numbers are written in lines that start with TEL;TYPE=CELL etc. So when I want to make updates in the phone I have to insert all the line that contains the phone and the above TEL...So when I do this with SQLdf commands (as you indicated) is says something that TEL column does not exist.something like this... – Tony Nov 22 '13 at 12:32
  • 1
    This is not clear. You might want to start another question with reproducible input and output. – G. Grothendieck Nov 22 '13 at 14:20