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.