[R] RMySQL - overwrite record, not table

Adrian Dusa dusa.adrian at gmail.com
Tue Aug 18 15:22:46 CEST 2009

whizvast wrote:
> Hi, useR-
> In RMySQL, how do I overwrite records? (equivalent to "replace" query).
> For example, suppose that dat2 is a newer data.frame than dat1. 
> con <- dbConnect(MySQL())
> res <- dbWriteTable(con, "DBname", dat1, row.names=F, append=T, replace=T)
> res <- dbWriteTable(con, "DBname", dat2, row.names=F, append=T, replace=T)
> This would not update/replace the dat1 records in "DBname" with newer
> records from dat2. 
> How would you solve the problem? Thanks=

In case it isn't too late (it's vacation time around), try:

dbWriteTable(con, "DBname", dat2, overwrite=TRUE, row.names=FALSE)

I believe you are confusing "DBname" with the DATABASE from the MySQL which
is not specified here but in dbConnect(). A correct (and complete)
connection to the MySQL should specify the database as well; for example:

con <- dbConnect(drv, user="myusername", password="mypass",
dbname="mydatabase", host="xxx.xxx.xxx.xxx")

Here, the "dbname" argument specifies the database used, the equivalent of
USE command in MySQL.
If "DBname" refers to a table in the database used with dbConnect(), then
the first command of dbWriteTable() that I indicated (without your "res <-",
it's a pure command to the MySQL connection, which should not be saved into
an object) will replace the table "DBname" with your "dat2" table.

I hope it helps,

View this message in context: http://www.nabble.com/RMySQL---overwrite-record%2C-not-table-tp24870097p25024769.html
Sent from the R help mailing list archive at Nabble.com.

More information about the R-help mailing list