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

I have two tables:
              Table "public.audit"  Column   |            Type      | Modifiers
------------+----------------------+-----------id         | integer              | (serial)record_id  | integer
    | not nullkey        | 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

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
>

On 19/02/2010 10:28, "zkn" <zkn@abv.bg> wrote:
> update audit set key = (select extension.number from extension where
> audit.record_id = extension_id)

No joy, I still get "UPDATE 0"

:-(

GTG

On 19/02/10 09:25, Gordon Ross wrote:
>
> UPDATE audit SET key = extension.number FROM extension WHERE audit.record_id
> = extension.number;
>
> But that returns saying "UPDATE 0"

Works here - are you sure you don't have any triggers interfering?

BEGIN;

CREATE TEMP TABLE audit (    id        int,    record_id int,    key       varchar,    PRIMARY KEY (id)
);

CREATE TEMP TABLE extension (    id       int,    number   int,    PRIMARY KEY (id)
);

INSERT INTO audit VALUES (1,1,''), (2,2,''), (3,3,'');
INSERT INTO extension VALUES (-1,1), (-2,2), (-3,3);

UPDATE audit SET key = extension.number
FROM extension
WHERE audit.record_id = extension.number;

SELECT * FROM audit ORDER BY id;

ROLLBACK;

CREATE TABLE
INSERT 0 3
INSERT 0 3
UPDATE 3 id | record_id | key
----+-----------+-----  1 |         1 | 1  2 |         2 | 2  3 |         3 | 3


--   Richard Huxton  Archonet Ltd

On 19/02/2010 10:36, "Richard Huxton" <dev@archonet.com> wrote:

> Works here - are you sure you don't have any triggers interfering?

Doh ! *blush*

Yes, I did, to stop anyone tampering with the audit table ;-)

Thanks for that.

GTG

On 19/02/10 10:58, Gordon Ross wrote:
> On 19/02/2010 10:36, "Richard Huxton"<dev@archonet.com>  wrote:
>
>> Works here - are you sure you don't have any triggers interfering?
>
> Doh ! *blush*
>
> Yes, I did, to stop anyone tampering with the audit table ;-)

There you go - it worked :-)

--   Richard Huxton  Archonet Ltd