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,
