pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is this a bug?
7+ messages / 5 participants
[nested] [flat]

* Is this a bug?
@ 1999-11-09 05:43  Imtiaz. S. M <imtiaz_sm@yahoo.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Imtiaz. S. M @ 1999-11-09 05:43 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Is this a bug?
@ 2001-11-13 05:20  Jack Gao <wei@gaofamily.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Jack Gao @ 2001-11-13 05:20 UTC (permalink / raw)
  To: pgsql-sql

Hi, There

When I use Update with Join, I got some problem.

I have a table as user config table (tblUserConfig), and another table as
config item table (tblConfigItem).

CREATE TABLE tblUserConfig (
 ID              integer NOT NULL DEFAULT nextval('tblUserConfig_pKey_ID')
PRIMARY KEY,
 UserID          integer NOT NULL,                         -- tblUser
 ItemID          integer NOT NULL,                         -- tblConfigItem
 Value           varchar(1024)
);


CREATE TABLE tblConfigItem (
 ID              integer NOT NULL DEFAULT nextval('tblConfigItem_pKey_ID')
PRIMARY KEY,
 Scope           integer NOT NULL DEFAULT 1,               -- 1: System
config, 2: Section config, 3: User config
 Name            varchar(64) NOT NULL,
 Description     varchar(64),
 DataType        integer NOT NULL DEFAULT 1,               -- 1: String, 2:
Numeric, 3: Boolean, 4: DateTime
 DefaultValue    varchar(1024)
);

Now, I would like to update tblUserConfig to set Value of 'UserClassID' to,
for example '1'. So I write a sql string like this:

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!!!

If I change it to:
Update tblUserConfig Set Value = '1' Where UserID = 1 And ItemID = (Select
ID From tblConfigItem Where Scope = 3 And Name = 'UserClassID');

It works fine.

So, is this a bug? or I make anything wrong?

Thanks

Noodle





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Is this a bug?
@ 2001-11-13 10:26  Carl van Tast <vanTast@Pivot.at>
  parent: Jack Gao <wei@gaofamily.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Carl van Tast @ 2001-11-13 10:26 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Is this a bug?
@ 2001-11-13 18:07  Jack Gao <wei@gaofamily.org>
  parent: Carl van Tast <vanTast@Pivot.at>
  0 siblings, 2 replies; 7+ messages in thread

From: Jack Gao @ 2001-11-13 18:07 UTC (permalink / raw)
  To: pgsql-sql


"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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Is this a bug?
@ 2001-11-14 09:28  Carl van Tast <vanTast@Pivot.at>
  parent: Jack Gao <wei@gaofamily.org>
  1 sibling, 0 replies; 7+ messages in thread

From: Carl van Tast @ 2001-11-14 09:28 UTC (permalink / raw)
  To: pgsql-sql

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 regards
 Carl van Tast



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Is this a bug?
@ 2001-11-15 16:24  Roland Roberts <roland@astrofoto.org>
  parent: Jack Gao <wei@gaofamily.org>
  1 sibling, 1 reply; 7+ messages in thread

From: Roland Roberts @ 2001-11-15 16:24 UTC (permalink / raw)
  To: pgsql-sql

>>>>> "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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Is this a bug?
@ 2001-11-15 17:50  Jack Gao <weigao@hotmail.com>
  parent: Roland Roberts <roland@astrofoto.org>
  0 siblings, 0 replies; 7+ messages in thread

From: Jack Gao @ 2001-11-15 17:50 UTC (permalink / raw)
  To: pgsql-sql


"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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2001-11-15 17:50 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-09 05:43 Is this a bug? Imtiaz. S. M <imtiaz_sm@yahoo.com>
2001-11-13 05:20 Is this a bug? Jack Gao <wei@gaofamily.org>
2001-11-13 10:26 ` Carl van Tast <vanTast@Pivot.at>
2001-11-13 18:07   ` Jack Gao <wei@gaofamily.org>
2001-11-14 09:28     ` Carl van Tast <vanTast@Pivot.at>
2001-11-15 16:24     ` Roland Roberts <roland@astrofoto.org>
2001-11-15 17:50       ` Jack Gao <weigao@hotmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox