Is there a COPY equivalent for updates?
eg I've
create table t1 ( id int primary key, col1 int, col2 int, col3 varchar(32)
);
and a CSV file
10,4,5,"abc"
13,7,3,"def"
18,12,77,"ghi"
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...
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?
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.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it