Thread: SubQuery

Hi!

I am using MyTable to serve the "next number"
functionality like PGSQL embeded "sequence" offers.
The only difference of the two is MyTable has 2 more
fields - "ID" and "Starting Effective Date".

The UPDATE SQL fails when I am trying to update
record
ID1 | 2001-08-01    |         11

to
ID1 | 2001-08-01    |         12

What mistake have I made?

CN
=================================
database1=# select * from mytable;id  | effectivedate | nextnumber
-----+---------------+------------ID1 | 2001-06-01    |         10ID1 | 2001-07-01    |          3ID1 | 2001-08-01    |
       11ID2 | 2001-09-01    |         35
 
(4 rows)

database1=# SELECT EffectiveDate,NextNumber FROM
mytable s1
database1-# WHERE id='ID1' AND EffectiveDate=
database1-# (SELECT MAX(EffectiveDate) FROM mytable
s2
database1(# WHERE s1.id=s2.id AND
s2.EffectiveDate<=CURRENT_DATE);effectivedate | nextnumber
---------------+------------2001-08-01    |         11
(1 row)

database1=# UPDATE mytable SET
NextNumber=NextNumber+1
database1-# WHERE id='ID1' AND EffectiveDate=
database1-# (SELECT MAX(s2.EffectiveDate) FROM
mytable s2
database1(# WHERE s2.id=id AND
s2.EffectiveDate<=CURRENT_DATE);
UPDATE 0
database1=# UPDATE mytable SET
NextNumber=NextNumber+1
database1-# WHERE id='ID2' AND EffectiveDate=
database1-# (SELECT MAX(s2.EffectiveDate) FROM
mytable s2
database1(# WHERE s2.id=id AND
s2.EffectiveDate<=CURRENT_DATE);
UPDATE 1
database1=# select * from mytable;id  | effectivedate | nextnumber
-----+---------------+------------ID1 | 2001-06-01    |         10ID1 | 2001-07-01    |          3ID1 | 2001-08-01    |
       11ID2 | 2001-09-01    |         36
 
(4 rows)

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com

On Thu, 4 Oct 2001 cnliou@eurosport.com wrote:

> What mistake have I made?
> 
> database1=# UPDATE mytable SET
> NextNumber=NextNumber+1
> database1-# WHERE id='ID1' AND EffectiveDate=
> database1-# (SELECT MAX(s2.EffectiveDate) FROM
> mytable s2
> database1(# WHERE s2.id=id AND
> s2.EffectiveDate<=CURRENT_DATE);
> UPDATE 0

I think you may need to disambiguate the s2.id=id 
in the subquery to s2.id=mytable.id since both 
have ids and it's probably assuming that means
s2.id=s2.id.  
After changing that I get an UPDATE 1.