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
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 |