Bug or syntax error in my update query with a FROM statement ?
От | ARP |
---|---|
Тема | Bug or syntax error in my update query with a FROM statement ? |
Дата | |
Msg-id | 00d701c1eb6f$478e7460$0100a8c0@arp.homelinux.org обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: