Обсуждение: Is this a bug?
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
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
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
"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
>>>>> "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
"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