Re: Bulk loading/merging

On 06/02/2006, Michael Artz wrote:

hmm, I don't quite understand what you are saying and I think my basic
misunderstanding is how to use the UPDATE ... WHERE EXISTS to merge data in
bulk. Assuming that I bulk COPYed the data into a temporary table, I'd need
to issue an UPDATE for each row in the newly created table, right?

For example, for a slightly different key,count schema:

CREATE TABLE kc (key integer, count integer);

and wanting to merge the following data by just updating the count for a
given key to the equivalent of OLD.count + NEW.count:

1,10

2,15

3,45

1,30

How would I go about using UPDATE ... WHERE EXISTS to update the "master" kc
table from a (temporary) table loaded with the above data?

May be, this method could help you:

CREATE TEMP TABLE clip_temp (

cids int8 NOT NULL,

clip_id int8 NOT NULL,

mentions int4 DEFAULT 0,

CONSTRAINT pk_clip_temp PRIMARY KEY (cids, clip_id))

)

insert data into this temporary table...

then do:

UPDATE clip_category SET mentions=clip_temp.mentions

FROM clip_temp

WHERE clip_category.cids=clip_temp.cids

AND clip_category.clip_id=clip_temp.clip_id

DELETE FROM clip_temp USING clip_category

WHERE clip_temp.cids=clip_category.cids

AND clip_temp.clip_id=clip_category.clip_id

INSERT INTO clip_category (cids, clip_id, mentions)

SELECT * FROM clip_temp

DROP TABLE clip_temp;

Best regards,

ahmad fajar,