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