Re: Bug or syntax error in my update query with a FROM statement ?

> Here's the "wrong" query :
> update utilisateur set login='*', password='*' from utilisateur a, ancien b where a.util_id=b.util_id and b.anc_deces
isnot null and (a.login<>'*' or a.password<>'*'); 
>
> Here's the "correct" one :
> update utilisateur set login='*', password='*' from ancien b where utilisateur.util_id=b.util_id and b.anc_deces is
notnull and (utilisateur.login<>'*' or utilisateur.password<>'*'); 
>
> Both queries work the same when there's nothing to update, (i.e. select count(*) from utilisateur a, ancien b where
a.util_id=b.util_idand b.anc_deces is not null and (a.login<>'*' or a.password<>'*') returns 0). 
> But the first one ("wrong") updates ALL rows of table 'utilisateur' when the above count() is not 0, where the second
one("correct") updates only the rows needed. 
>
> What's wrong with the first query : me or the parser ? I guess the problem comes from the fact that table
'utilisateur'appears twice and is aliased which may confuse the parser in the "wrong" query... 
>

The first query SHOULD update all rows, because there is NO restriction
on the utilisateur table - a.login <> '*' is NOT the same as
utilisateur.login <> '*' - the former qualifies the FROM clause, and the
latter applies to the UPDATE itself.


I hope, it helps...

Dima