From: Gordon Ross <gr306@ucs.cam.ac.uk>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Unable to get UPDATE ... FROM syntax correct
Date: Fri, 19 Feb 2010 09:25:48 +0000
Message-ID: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk> (raw)
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 = extension.id
I tried:
UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id
= extension.number;
But that returns saying "UPDATE 0"
However, doing:
SELECT audit.record_id, extension.number FROM audit, extension WHERE
audit.record_id = extension.id;
Works fine.
Can someone tell me what I'm doing wrong ?
Thanks,
GTG
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: gr306@ucs.cam.ac.uk
Subject: Re: Unable to get UPDATE ... FROM syntax correct
In-Reply-To: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
* 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