Обсуждение: 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.