title: r - dbWriteTable or a MySQL Delete query?
body: <p>I can't seem to find the answer to this anywhere.  I am reading a csv into a data frame using the read.csv function.  Then I am writing the data frame contents to a mysql table using dbWriteTable.  This works great for the initial run to create the table, but I each run after this needs to do either an insert or an update depending on whether the record already exists in the table.</p>
  
  <p>The 1st column in the data frame is the primary key, and the other records contain data that might change every time I pull a new copy of the csv.  Each time I pull the  CSV, if the primary key already exists, I want it to update that record with the new data, and if the primary key does not exist(eg: a new key since the last run), I want it to just insert the record into the table.</p>
  
  <p>This is my current dbWriteTable.  This creates the table just fine the 1st time it's run, and also inserts a "Timestamp" column into the table that is set to "on update CURRENT_TIMESTAMP" so that I know when each record was last updated.</p>
  
  <pre><code>dbWriteTable(mydb, value=csvData, name=Table, row.names=FALSE, field.types=list(PrimaryKey="VARCHAR(10)",Column2="VARCHAR(255)",Column3="VARCHAR(255)",Timestamp="TIMESTAMP"), append=TRUE)
  </code></pre>
  
  <p>Now the next time I run this, I simply want it to update any PrimaryKeys that are already in the table, and add any new ones.  I also don't want to lose any records in the event a PrimaryKey disappears from the CSV source.</p>
  
  <p>Is it possible to do this kind of  update using dbWriteTable, or some other R function?</p>
  
  <p>If that's not possible, is it possible to just run a mysql query that would delete any duplicate PrimaryKey records and keep just the 1 record with the most current timestamp?  So I would run a dbWriteTable to append the new data, and then run a MySQL query to prune out the older records.</p>
  
  <p>Obviously I couldn't define that 1st column as an actual PrimaryKey in the DB as my append/delete solution wouldn't work due to duplicate keys, and that's fine, I can always add an auto increment integer column to the table for the "real" primary key if needed.</p>
  
  <p>Thoughts?</p>