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

[Richard Huxton <dev(at)archonet(dot)com>]

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