Re: What's the fastest way to do this?
От | Orion |
---|---|
Тема | Re: What's the fastest way to do this? |
Дата | |
Msg-id | 9sp764$i3c$1@news.tht.net обсуждение исходный текст |
Ответ на | Re: What's the fastest way to do this? (Einar Karttunen <ekarttun@cs.Helsinki.FI>) |
Список | pgsql-general |
I tried your idea but I cant get it to work. Here's the SQL I used: CREATE TEMP table mfps_action_codes_394_tmp ( code text, description text); INSERT into mfps_action_codes_394_tmp (code,description) VALUES ('$','Dun Notice Printed'); INSERT into mfps_action_codes_394_tmp (code,description) VALUES ('&','Continuity Speedup'); INSERT into mfps_action_codes_394_tmp (code,description) VALUES ('*','Expiring CC Notification-Conty'); INSERT into mfps_action_codes_394_tmp (code,description) VALUES ('0','Return Authorization'); UPDATE mfps_action_codes_394 SET description = x.description FROM mfps_action_codes_394 AS rt, mfps_action_codes_394_tmp AS x WHERE rt.code = x.code; INSERT INTO mfps_action_codes_394 (code,description) SELECT code,description FROM mfps_action_codes_394_tmp AS x WHERE NOT EXISTS (SELECT 1 FROM mfps_action_codes_394 WHERE code = x.code); What ends up happening is that the UPDATE will change EVERY exsisting description to 'Dun Notice Printed' I can't find any documentation as to how to use the FROM keyword on the UPDATE command lest I'm sure I'd be able to figure this out myself. > Even faster is to: > > 1. load the data into a temporary table (even faster is to load into a > permanent > table -- just truncate it first in each run). Let's call this table > ImportTable. > > 2. update the existing records > UPDATE rt > SET a = t.a, b = x.b, c = x.c > FROM RealTable AS rt, ImportTable AS x > WHERE rt.pk = x.pk > > 3. insert the new records > INSERT INTO RealTable(pk, a, b, c) > SELECT pk, a, b, c > FROM ImportTable AS x > WHERE NOT EXISTS (SELECT 1 FROM RealTable WHERE pk = x.pk) > > > 'pk' is the primary key of the table, or, at worst, a UNIQUEly INDEXed > column > combination. > > This avoids the expensive DELETE operation (DBMSs are generally better at > INSERTing and UPDATEing than DELETEing, and remember that the DELETEs have > to > go through the transaction log). > > Don't use cursors if you can help it -- cursors can be up to several > orders of > magnitude slower, and usually at least 4 times slower. > > Using an import table allows you to sanitize the data by insert a step to > do this between steps 1 and 2. > > > Cheers, > > Colin
В списке pgsql-general по дате отправления: