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

From: "ARP" <arnaud(dot)mlist1(at)free(dot)fr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Bug or syntax error in my update query with a FROM statement ?
Date: 2002-04-24 09:06:09
Message-ID: 00d701c1eb6f$478e7460$0100a8c0@arp.homelinux.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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...

I've spent some time on this, so if it can help someone...

Arnaud

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jakub Ouhrabka 2002-04-24 09:14:36 Re: calling functions in select
Previous Message Tille, Andreas 2002-04-24 08:39:18 Re: Locale settings