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

[hadley wickham]

> 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

I don't think this will be any faster - why would creating a new table
be faster than updating existing rows?

I've never had a problem with using large numbers of SQL update
statements (in the order of hundreds of thousands) to update a table
and having them complete in a reasonable time (a few minutes).  How
heavily indexed is the field you are updating?  You may be able to get
some speed improvements by turning off indices before the update and
back on again afterwards (highly dependent on your database system
though).

I would strongly suspect your bottleneck lies elsewhere (eg. when
generating the statements in R, or using ODBC to send them)

Hadley