From: zkn <zkn@abv.bg>
To: pgsql-sql@postgresql.org
Cc: Gordon Ross <gr306@ucs.cam.ac.uk>
Subject: Re: Unable to get UPDATE ... FROM syntax correct
Date: Fri, 19 Feb 2010 12:28:10 +0200
Message-ID: <FA778767-AE75-4A0C-A60E-2EC0E3F69841@abv.bg> (raw)
In-Reply-To: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
References: <C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk>
update audit set key = (select extension.number from extension where audit.record_id = extension_id)
On 19.02.2010, at 11:25, Gordon Ross wrote:
> 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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
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: zkn@abv.bg, gr306@ucs.cam.ac.uk
Subject: Re: Unable to get UPDATE ... FROM syntax correct
In-Reply-To: <FA778767-AE75-4A0C-A60E-2EC0E3F69841@abv.bg>
* 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