whizvast wrote:
>
> 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!
>
Sorry for the late reply (I'm on my vacation). If you want to replace a
variable instead of the whole dataframe, I wrote a function about a year ago
and I used it succesfully a few times.
Try this:
"dbUpdateVars" <-
function(conn, dbtable, dataframe=NULL, primary, vars) {
if (!dbExistsTable(conn, dbtable)) {
stop("The target table \"", dbtable, "\" doesn't exist in the
database \"", dbGetInfo(conn)$dbname, "\"\n\n", call. = FALSE)
}
if (is.null(dataframe)) {
stop("The source dataframe is missing, with no default\n\n", call. =
FALSE)
}
if (!(toupper(primary) %in% toupper(names(dataframe)))) {
stop("The primary key variable doesn't exist in the source
dataframe\n\n", call. = FALSE)
}
if (!all(toupper(vars) %in% toupper(names(dataframe)))) {
stop("One or more variables don't exist in the source
dataframe\n\n", call. = FALSE)
}
if (!(toupper(primary) %in% toupper(dbListFields(con, dbtable)))) {
stop("The primary key variable doesn't exist in the target
table\n\n", call. = FALSE)
}
if (!all(toupper(vars) %in% toupper(dbListFields(con, dbtable)))) {
stop("One or more variables don't exist in the target table\n\n",
call. = FALSE)
}
if(length(vars) > 1) {
pastedvars <- paste("'", apply(dataframe[, vars], 1, paste,
collapse="', '"), "'", sep="")
}
else {
pastedvars <- paste("'", dataframe[, vars], "'", sep="")
}
varlist <- paste(dbtable, "(", paste(c(primary, vars), collapse=", "),
")", sep="")
datastring <- paste("(", paste(paste(dataframe[, primary], pastedvars,
sep=", "), collapse="), ("), ")", sep="")
toupdate <- paste(paste(vars, "=VALUES(", vars, ")", sep=""),
collapse=", ")
sqlstring <- paste("INSERT INTO", varlist, "VALUES", datastring, "ON
DUPLICATE KEY UPDATE", toupdate)
dbSendQuery(conn, sqlstring)
}
I hopw it helps you,
Adrian
--
View this message in context:
http://www.nabble.com/RMySQL---overwrite-record%2C-not-table-tp24870097p25120044.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.
