Re: Unable to get UPDATE ... FROM syntax correct

On 19/02/10 09:25, Gordon Ross wrote:
>
> UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id
> = extension.number;
>
> But that returns saying "UPDATE 0"

Works here - are you sure you don't have any triggers interfering?

BEGIN;

CREATE TEMP TABLE audit (    id        int,    record_id int,    key       varchar,    PRIMARY KEY (id)
);

CREATE TEMP TABLE extension (    id       int,    number   int,    PRIMARY KEY (id)
);

INSERT INTO audit VALUES (1,1,''), (2,2,''), (3,3,'');
INSERT INTO extension VALUES (-1,1), (-2,2), (-3,3);

UPDATE audit SET key = extension.number
FROM extension
WHERE audit.record_id = extension.number;

SELECT * FROM audit ORDER BY id;

ROLLBACK;

CREATE TABLE
INSERT 0 3
INSERT 0 3
UPDATE 3 id | record_id | key
----+-----------+-----  1 |         1 | 1  2 |         2 | 2  3 |         3 | 3


--   Richard Huxton  Archonet Ltd