Обсуждение: Bug or syntax error in my update query with a FROM statement ?
Hi, I've had recently a problem with an update query. Here's the simplified shema of the two tables implied :
Table "utilisateur"
Column | Type | Modifiers
-------------+--------------------------+-----------
util_id | integer | not null
login | character varying(11) |
password | character varying(20) |
Table "ancien"
Column | Type | Modifiers
-----------------+--------------------------+-----------------------------------------------------
util_id | integer |
anc_deces | timestamp with time zone |
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 not
nulland (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'
appearstwice and is aliased which may confuse the parser in the "wrong" query...
I've spent some time on this, so if it can help someone...
Arnaud
> 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
Thanks for your reply.
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
thecount is not zero ? I would agree with you if ALL rows were updated in any case or did I miss something ?
Arnaud
>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.
>"Dima Tkach" <dmitry@openratings.com> a écrit dans le message de news: 3CC6A7AF.3070801@openratings.com...
>> 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_decesis 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
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
secondone >("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...
>>
"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
Thanks a lot, that's very clear for me now ! Arnaud >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?