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?