On Thu, 4 Oct 2001 [EMAIL PROTECTED] 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. ---------------------------(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