Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is this a bug?

Is this a bug?

From
"Imtiaz. S. M"
Date:
I have the following table named 'emp_test' whose structure is as
follows

Table    = emp_test
+----------------------------------+----------------------------------+-------+

|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+

| employee_num            | char()                           |    10 |
| salary                           | int4
|     4 |
+----------------------------------+----------------------------------+-------+



The table contains the following records;

employee_num|salary
------------+------
100         |  1000
200         |  2000
300         |  3000
400         |  4000
500         |  5000
100         |  1111
200         |  2222
300         |  3333
400         |  4444
500         |  5555
(10 rows)


Now I create a view with the following SQL statement.

create view emp_view as select employee_num,sum(salary) from emp_test
group by employee_num;

Then I give the following query on the view.

select * from emp_view
for which the output is as follows.

employee_num|  sum
------------+-----
100         | 2111
200         | 4222
300         | 6333
400         | 8444
500         |10555
(5 rows)

This is perfectly right

Now I give the following query

select count(*) from emp_view
and the output is as follows

count
-----   2   2   2   2   2
(5 rows)

This is what does not make sense to me because I am expecting the no. of
rows to be returned

I need to issue these kind of queries because I would like to find out
the number or percentage of people earning a salary
in a particular range and etc.
I have solved the problem by creating temporary tables instead of views
where these kind of queries work perfectly fine.
But this does not seem to be a good idea since I have to port my
software to different RDBMSs' later.

Can somebody help me?
Thanks in advance

regards
Imtiaz

Re: Is this a bug?

From
Carl van Tast
Date:
On Mon, 12 Nov 2001 21:20:44 -0800, "Jack Gao" <wei@gaofamily.org>
wrote:

>Update tblUserConfig Set Value = '1' From tblUserConfig As a Join
>tblConfigItem As b On a.ItemID = b.ID And b.Scope = 3 Where a.UserID = 1 And
>b.Name = 'UserClassID';
>
>It always update all records in tblUserConfig table!!!

Jack,

don't include the table you want to update in the FROM clause. Just
try:

Update tblUserConfig Set Value = '1' From tblConfigItem As b Where tblUserConfig.ItemID = b.ID   And b.Scope = 3   And
tblUserConfig.UserID= 1   And b.Name = 'UserClassID';
 

Kind regardsCarl van Tast

Re: Is this a bug?

From
Carl van Tast
Date:
On Tue, 13 Nov 2001 10:07:56 -0800, "Jack Gao" <wei@gaofamily.org>
wrote:
>"Carl van Tast" <vanTast@Pivot.at> wrote in message
>news:73t1vtk61pcsob9pa0p32a9eou3a31tolh@4ax.com...
>> Update tblUserConfig
>>   Set Value = '1'
>>   From tblConfigItem As b
>>   Where tblUserConfig.ItemID = b.ID
>>     And b.Scope = 3
>>     And tblUserConfig.UserID = 1
>>     And b.Name = 'UserClassID';
>
>So, I can't use JOIN in UPDATE?

You cannot use explicit JOIN syntax. But you use joins implicitly.
Just construct your WHERE clause as if you had tblUserConfig in your
FROM clause.

>
>Thanks for your reply
>
>Jack
Kind regardsCarl van Tast

Re: Is this a bug?

From
"Jack Gao"
Date:
"Carl van Tast" <vanTast@Pivot.at> wrote in message
news:73t1vtk61pcsob9pa0p32a9eou3a31tolh@4ax.com...
> On Mon, 12 Nov 2001 21:20:44 -0800, "Jack Gao" <wei@gaofamily.org>
> wrote:
>
> >Update tblUserConfig Set Value = '1' From tblUserConfig As a Join
> >tblConfigItem As b On a.ItemID = b.ID And b.Scope = 3 Where a.UserID = 1
And
> >b.Name = 'UserClassID';
> >
> >It always update all records in tblUserConfig table!!!
>
> Jack,
>
> don't include the table you want to update in the FROM clause. Just
> try:
>
> Update tblUserConfig
>   Set Value = '1'
>   From tblConfigItem As b
>   Where tblUserConfig.ItemID = b.ID
>     And b.Scope = 3
>     And tblUserConfig.UserID = 1
>     And b.Name = 'UserClassID';
>
> Kind regards
>  Carl van Tast

So, I can't use JOIN in UPDATE?

Thanks for your reply

Jack

Re: Is this a bug?

From
Roland Roberts
Date:
>>>>> "Jack" == Jack Gao <wei@gaofamily.org> writes:
   Jack> So, I can't use JOIN in UPDATE?

You are trying to update two different tables.  Use a transaction
instead.
   begin transaction;   update foo ....;   update bar ....;   end transaction;

roland
--            PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375

Re: Is this a bug?

From
"Jack Gao"
Date:
"Roland Roberts" <roland@astrofoto.org> wrote in message
news:m2ofm4gfxs.fsf@tycho.rlent.pnet...
> >>>>> "Jack" == Jack Gao <wei@gaofamily.org> writes:
>
>     Jack> So, I can't use JOIN in UPDATE?
>
> You are trying to update two different tables.  Use a transaction
> instead.
>
>     begin transaction;
>     update foo ....;
>     update bar ....;
>     end transaction;
>
> roland

No, I just need to update one table, which is tblUserConfig.

> --
>        PGP Key ID: 66 BC 3B CD
> Roland B. Roberts, PhD                             RL Enterprises
> roland@rlenter.com                     76-15 113th Street, Apt 3B
> roland@astrofoto.org                       Forest Hills, NY 11375
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org