Re: Bulk loading/merging

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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,

 

 

 

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux