title: R SQL Update statement with Inner Join
body: <p>The below SQL statement works in Microsoft Access but not in R when using SQLDF.  I believe it is because SQLDF is SQLite and that doesn't support joins in update statements.  Problem is, I don't know how to get around the issue.
  How do I accomplish the below update in R?</p>
  
  <pre><code>    UPDATE NewData INNER JOIN Inforce ON NewData.ContractNumber = Inforce.PolicyNumber SET Inforce.IncomeRider = "N" WHERE (((NewData.[Rider Termination Date])&lt;=[Inforce].[FileDate]));
  </code></pre>
  
  <p>I tried in R:</p>
  
  <pre><code>sqldf('UPDATE NewData INNER JOIN Inforce ON NewData.ContractNumber = Inforce.PolicyNumber SET Inforce.IncomeRider = "Y" WHERE ((NewData.[Rider Termination Date])&gt;[Inforce].[FileDate])')
  </code></pre>
  
  <p>And received this error:</p>
  
  <blockquote>
    <p>Error in sqliteSendQuery(con, statement, bind.data) : 
      error in statement: near "INNER": syntax error</p>
  </blockquote>
  
  <p>This doesn't have to be accomplished by a SQL statement but thought it would be easiest for me since I already had a working SQL statement.</p>
  
  <pre><code>NewData &lt;- data.frame(ContractNumber=c(1,2,3),
                        RiderTerminationDate=c(as.Date(c("2015-01-01","2015-06-01","2016-05-01"))))
  
  Inforce &lt;- data.frame(PolicyNumber=c(1,2,3,4),
                        IncomeRider=c('Y','N','N','N'),
                        FileDate=c(as.Date(c("2014-01-01","2013-01-01","2016-08-01","2016-02-01"))))
  </code></pre>
  
  <p>Resulting IncomeRider should be 'Y','N','Y','N'</p>