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 is not 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 not null 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_id and 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