[R] INSERT OR UPDATE

[Mikkel Grum]

Thanks Steven. It obviously makes sense to loop on the much smaller dataset that is being added than the set of everything that might already be in the database. I've added your message in plain text, so that others can see it too. Mikkel 

From: Steven Kennedy <stevenkennedy2263 at gmail.com>
Subject: Re: [R] INSERT OR UPDATE
To: "Mikkel Grum" <mi2kelgrum at yahoo.com>
Cc: "R Help" <r-help at r-project.org>
Date: Monday, May 2, 2011, 5:15 PM


Rather than selecting all the keys, then having R loop through them, why not have postgres do it for you with something like:
 
#go through each line in our entry table
for (i in 1:dim(tbl)[1]){
    #check if the pkey already exists
    q <- paste ("SELECT key1, key2 FROM tabl WHERE key1=",tbl[i,1],"
        AND key2=",tbl[i,1]",sep="") 
    yes <- sqlQuery(pg, q, as.is = TRUE)
    if (dim(yes)[1] == 1){
        #update the row if it exists
        sqlUpdate(pg, tbl[i,],"tbl", index = c("key1", "key2"))
    } else {
        #add the row if it doesn't
        sqlSave(pg, tbl[i,], "tbl", append = TRUE, rownames = FALSE)
    }
}
 
This should work fine for small or large tables (especially if you index the large table that doesn't change much).