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 | 10 ID1 | 2001-07-01 | 3 ID1 | 2001-08-01 | 11 ID2 | 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 | 10 ID1 | 2001-07-01 | 3 ID1 | 2001-08-01 | 11 ID2 | 2001-09-01 | 36 (4 rows) -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
