From: Richard Huxton <dev@archonet.com>
To: Gordon Ross <gr306@ucs.cam.ac.uk>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Unable to get UPDATE ... FROM syntax correct
Date: Fri, 19 Feb 2010 10:36:21 +0000
Message-ID: <4B7E69A5.4060705@archonet.com> (raw)
In-Reply-To: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
References: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, gr306@ucs.cam.ac.uk
Subject: Re: Unable to get UPDATE ... FROM syntax correct
In-Reply-To: <4B7E69A5.4060705@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox