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

"ARP" <arnaud.mlist1@free.fr> writes:
> I would tend to agree with you but why doesn't it update ANY row when
> count() equals zero then, and ALL rows only when the count is not zero?

Because you've written a three-way join.  The target table is
unconstrained --- but if there are no pairs of rows of the other two
tables that satisfy the WHERE clause, there will be no three-way
combinations that do either, and so there are no rows selected to be
updated.  On the other hand, if any pair of rows of the other two
table satisfy WHERE, *all* rows of the target table will be joined
to them to make updatable rows.

It's useful to think of the behavior of UPDATE as exactly like a
SELECT, except that rather than reporting the selected rows to you,
it changes them per the SET target list.  Had you written

select utilisateur.* from utilisateur, utilisateur a, ancien b
where a.util_id=b.util_id and b.anc_deces is not null and
      (a.login<>'*' or a.password<>'*');

the error would be moderately obvious, no?

            regards, tom lane