Re: Update with last known location?

James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:

> Given the data is so large I don't want to be taking the data out
> to a CSV or whatever and then loading it back in. I'd like to do
> this within the database using SQL. I thought I would be able to
> do this using a LOOP to be honest.

I would be amazed if you couldn't do this with  a single UPDATE
statement.  I've generally found declarative forms of such work to
be at least one order of magnitude faster than going to either a PL
or a script approach.  I would start by putting together a SELECT
query using window functions and maybe a CTE or two to list all the
primary keys which need updating and the new values they should
have.  Once that SELECT was looking good, I would put it in the
FROM clause of an UPDATE statement.

That should work, but if you are updating a large percentage of the
table, I would go one step further before running this against the
production tables.  I would put a LIMIT on the above-mentioned
SELECT of something like 10000 rows, and script a loop that
alternates between the UPDATE and a VACUUM ANALYZE on the table.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company