pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs 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