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 по дате отправления:

Предыдущее
От: "Tille, Andreas"
Дата:
Сообщение: Re: Locale settings
Следующее
От: Jakub Ouhrabka
Дата:
Сообщение: Re: calling functions in select