Skip site navigation (1) Skip section navigation (2)

Re: Bug regarding update.. from and aggregates

From: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
To: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
Cc: pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org>
Subject: Re: Bug regarding update.. from and aggregates
Date: 2005-06-17 15:27:10
Message-ID: 42B2EBCE.30706@pse-consulting.de (view raw or flat)
Thread:
Lists: pgadmin-hackers
This should go to pgsql-sql or so.

Florian G. Pflug wrote:
> Hi
> 
> When executing the following statements:
> create schema bugtest ;
> drop table bugtest.t1 ;
> drop table bugtest.t2 ;
> create table bugtest.t1(id int4, maximum int4, dummy int4) ;
> create table bugtest.t2(id int4, entry int4) ;
> insert into bugtest.t1 (id) values (1) ;
> insert into bugtest.t1 (id) values (2) ;
> insert into bugtest.t2 (id, entry) values (1, 1) ;
> insert into bugtest.t2 (id, entry) values (1, 2) ;
> insert into bugtest.t2 (id, entry) values (2, 3) ;
> insert into bugtest.t2 (id, entry) values (2, 4) ;
> update bugtest.t1 set maximum = max(t2.entry) from bugtest.t2
>     where t1.id = t2.id ;
> 
> The last update given "One row affected" - and indeed, t1 looks
> like this after the update:
> id | maximum
> 1  | 4
> 2  | <NULL>
> 
> While I would expect:
> id | maximum
> 1  | 2
> 2  | 4
> 
> Is this I bug, or is the observed behaviour actually intended?

The query is probably not what you mean. You need to make the from 
clause specific, i.e. join t1 explicitely (mssql does so implicitely, 
pgsql not).

Regards,
Andreas

In response to

pgadmin-hackers by date

Next:From: svnDate: 2005-06-17 17:17:22
Subject: SVN Commit by andreas: r4312 - trunk/pgadmin3/xtra/pgagent
Previous:From: svnDate: 2005-06-17 14:24:43
Subject: SVN Commit by andreas: r4311 - trunk/pgadmin3/xtra/pgagent

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group