From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Gordon Ross <gr306(at)ucs(dot)cam(dot)ac(dot)uk> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Unable to get UPDATE ... FROM syntax correct |
Date: | 2010-02-19 10:36:21 |
Message-ID: | 4B7E69A5.4060705@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2010-02-19 10:56:07 | Re: sintax error |
Previous Message | Gordon Ross | 2010-02-19 10:32:03 | Re: Unable to get UPDATE ... FROM syntax correct |