Re: [SQL] COPY equivalent for updates

[Decibel!]

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?

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

BEGIN;
COPY temp_table FROM 'file';

INSERT INTO real_table SELECT * FROM temp_table;
COMMIT;

Almost certainly... databases like dealing with sets of data; what your proposing with AWK turns it into a ton of single-row statements. DELETE FROM real_table WHERE real_table_id IN (SELECT real_table_id FROM temp_table);

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  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828

smime.p7s
Description: S/MIME cryptographic signature