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

[Duncan Murdoch]

On 5/19/2006 11:17 AM, Duncan Murdoch wrote:
> 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?

Thanks to Robert McGehee and Bogdan Romocea for their responses.

Putting them together, I think the following will do what I want:

put the updates into a temporary table called updates

UPDATE bigtable AS a
   FROM updates AS b
   WHERE a.id = b.id
   SET a.col1 = b.col1

The FROM clause is a PostgreSQL extension.  This is not portable, but 
MySQL does it with different syntax:

UPDATE bigtable AS a, updates AS b
   WHERE a.id = b.id
   SET a.col1 = b.col1

I don't think SQLite supports updating one table from another.

Duncan Murdoch