Unable to get UPDATE ... FROM syntax correct

From: Gordon Ross <gr306(at)ucs(dot)cam(dot)ac(dot)uk>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Unable to get UPDATE ... FROM syntax correct
Date: 2010-02-19 09:25:48
Message-ID: C7A4099C.4726%gr306@staff-ad.csi.cam.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message zkn 2010-02-19 10:28:10 Re: Unable to get UPDATE ... FROM syntax correct
Previous Message iuri de araujo sampaio 2010-02-19 07:52:17 sintax error