[R] Fast update of a lot of records in a database?

[bogdan romocea]

Your approach seems very inefficient - it looks like you're executing
thousands of update statements. Try something like this instead:
#---build a table 'updates' (id and value)
...
#---do all updates via a single left join
UPDATE bigtable a LEFT JOIN updates b
ON a.id = b.id
SET a.col1 = b.value;
You may need to adjust the syntax.


> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Duncan Murdoch
> Sent: Friday, May 19, 2006 11:17 AM
> To: r-help at stat.math.ethz.ch
> Subject: [R] Fast update of a lot of records in a database?
>
> We have a PostgreSQL table with about 400000 records in it.  Using
> either RODBC or RdbiPgSQL, what is the fastest way to update
> one (or a
> few) column(s) in a large collection of records?  Currently we're
> sending sql like
>
> BEGIN
> UPDATE table SET col1=value WHERE id=id
> (repeated thousands of times for different ids)
> COMMIT
>
> and this takes hours to complete.  Surely there must be a quicker way?
>
> Duncan Murdoch
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>