[R] RMySQL - overwrite record, not table

whizvast whizvast at gmail.com
Thu Aug 20 17:49:18 CEST 2009


Hi, Adrian-

If you use "overwrite=T" parameter, you will overwrite the entire table, not
each record. this is the essence of my problem and i still haven't found out
right solution. i am thinking of writing my own MySQLwriteTable function...

Thank you for your answer anyway!




Adrian Dusa wrote:
> 
> 
> 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,
> Adrian
> 
> 

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




More information about the R-help mailing list