|
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, |
