Re: What's the fastest way to do this? - Mailing list pgsql-general

Ok, I've boiled this down to an easy to run test...

DROP TABLE test;

CREATE TABLE test (
        code    int4,
        description     text);

INSERT INTO test (code,description) VALUES (1,'OLD VALUE 1');
INSERT INTO test (code,description) VALUES (2,'OLD VALUE 2');

CREATE TEMP TABLE test_tmp (
        code    int4,
        description     text);

INSERT INTO test_tmp (code,description) VALUES (1,'NEW VALUE 1');
INSERT INTO test_tmp (code,description) VALUES (2,'NEW VALUE 2');
INSERT INTO test_tmp (code,description) VALUES (3,'NEW ENTRY 1');
INSERT INTO test_tmp (code,description) VALUES (4,'NEW ENTRY 2');

UPDATE test
        SET description = x.description
        FROM test AS rt, test_tmp AS x
        WHERE rt.code = x.code;

INSERT INTO test (code,description)
        SELECT code,description
        FROM test_tmp AS x
        WHERE NOT EXISTS
        (SELECT 1 FROM test WHERE code = x.code);

SELECT * FROM test;

And here's the results I get from that test

DROP
CREATE
INSERT 1894322 1
INSERT 1894323 1
CREATE
INSERT 1894350 1
INSERT 1894351 1
INSERT 1894352 1
INSERT 1894353 1
UPDATE 2
INSERT 0 2
 code | description
------+-------------
    1 | NEW VALUE 1
    2 | NEW VALUE 1
    3 | NEW ENTRY 1
    4 | NEW ENTRY 2
(4 rows)

And as far as I understand it I should be getting the following

 code | description
------+-------------
    1 | NEW VALUE 1
    2 | NEW VALUE 2
    3 | NEW ENTRY 1
    4 | NEW ENTRY 2

For some reason the update is updating ALL the old records to be
'NEW VALUE 1'.

So (A) is this a bug or (B) do I totally misunderstand that UPDATE statement
above.  I appears that it should update each row in table 'test' with the
description corresponding to the code in test_tmp.  If this is not the
proper way to do this, what is?