Received: from lh00.opsion.fr (lh00.opsion.fr [212.73.208.226]) by postgresql.org (8.11.3/8.11.4) with SMTP id f9416En58547 for ; Wed, 3 Oct 2001 21:06:14 -0400 (EDT) (envelope-from cnliou@eurosport.com) Received: from 10.1.1.8 [10.1.1.8] by lh00.opsion.fr; Thu, 4 Oct 2001 01:06:04 GMT Send-By: 203.66.130.112 with Mozilla/4.78 [en] (Win95; U) To: Subject: SubQuery From: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Date: Thu, 4 Oct 2001 01:06:04 GMT Message-id: <200110040106.04c2@lh00.opsion.fr> Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f9416Fn58548 X-Archive-Number: 200110/40 X-Sequence-Number: 4995 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