From: Gordon Ross <gr306@ucs.cam.ac.uk>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Fri, 19 Feb 2010 09:25:48 +0000
Subject: Unable to get UPDATE ... FROM syntax correct
Thread-Topic: Unable to get UPDATE ... FROM syntax correct
Thread-Index: AcqxRYVBi+vxbjmqykmMcNsESgkkTA==
Message-ID: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
Accept-Language: en-US
Content-Language: en
acceptlanguage: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

I have two tables:

               Table "public.audit"
   Column   |            Type      | Modifiers
------------+----------------------+-----------
 id         | integer              | (serial)
 record_id  | integer              | not null
 key        | character varying    |
(...)


      Table "public.extension"
  Column |   Type     |  Modifiers
---------+------------+------------
 id      | integer    | (serial)
 number  | integer    |
(...)


The column "key" was recently added to the "audit" table, and I wish to
populate it with the value form the "number" column in the extension table.
The join between the tables is audit.record_id =3D extension.id

I tried:

UPDATE audit SET key =3D extension.number FROM extension WHERE audit.record=
_id
=3D extension.number;

But that returns saying "UPDATE 0"

However, doing:

SELECT audit.record_id, extension.number FROM audit, extension WHERE
audit.record_id =3D extension.id;

Works fine.

Can someone tell me what I'm doing wrong ?

Thanks,

GTG