Message-ID: <4B7E69A5.4060705@archonet.com>
Date: Fri, 19 Feb 2010 10:36:21 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9pre) Gecko/20100213 Lightning/1.0b1 Shredder/3.0.2pre
MIME-Version: 1.0
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
References: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
In-Reply-To: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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