[R] RMySQL - overwrite record, not table

[Adrian Dusa]

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.