Re: COPY equivalent for updates

On Jul 15, 2008, at 1:10 AM, Ivan Sergio Borgonovo wrote:
> I'd like to
>
> UPDATE t1 (col1, col2, col3) from file with @1 as primary key;
> or
> UPDATE t1 (col1, col2, col3) from file where @1=id;
>
> sort of...

Sorry, there's nothing like COPY for UPDATE.

> Otherwise what is the fastest approach?
>
>
> I can think of 2 approaches:
> 1)load a temp table with COPY
>
> update t1 set col1=temp_t1.col1, col2=temp_t1.col2
> where t1.id=temp_t1.id;
>
> 2) use awk to generate update statements.
>
> Supposing I could neglect the awk execution time, will COPY + UPDATE
> be faster than executing a list of UPDATE?

Almost certainly... databases like dealing with sets of data; what
your proposing with AWK turns it into a ton of single-row statements.

Depending on what you're doing, it might well be fastest to...

BEGIN;
COPY temp_table FROM 'file';
DELETE FROM real_table WHERE real_table_id IN (SELECT real_table_id
FROM temp_table);
INSERT INTO real_table SELECT * FROM temp_table;
COMMIT;

> Considering I've to deal with a where clauses anyway... when (and
> if) should I create an index on the id of temp_t1?
> t1 will contain 700-1M records while I may update a maximum of 20K a
> time.

--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828