Re: Unable to get UPDATE ... FROM syntax correct

[zkn <zkn(at)abv(dot)bg>]

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>