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