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 <stevenkennedy2...@gmail.com>
Subject: Re: [R] INSERT OR UPDATE
To: "Mikkel Grum" <mi2kelg...@yahoo.com>
Cc: "R Help" <r-help@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).
______________________________________________
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.
