Re: What's the fastest way to do this?

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