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. ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
