Обсуждение: update impossible, constraint prevents it but it shouldn't

I apologize if I'm running an old version (7.2.1) maybe this has been
fixed or maybe it's not a bug but I can't look at the changelogs now.

UPDATE has a non-standard FROM clause where you can do joins etc so
columns from other tables can appear in WHERE.

I run the following query:

update personal set closed_date = flt_date + 20
from enr inner join personal p on enr.id = p.id_enr
where (...columns from enr, columns from personal...)

this runs for a while then fails because of a constraint but if I
rewrite the above as a select with the exact same from and where clauses
looking for any offending rows there are none. The constraint says
flt_date <= closed_date so it's clear the update cannot violate it.

- Has anyone encountered anything like this?

I can fix it creating functions that take the primary key and return the
value I want from the related table I'm referencing (it's 1:1) so I can
take the inner join out of the from, I know this works but I'd like to
avoid so many function calls.

Thanks!
Lucia

On Mon, Jun 26, 2006 at 01:00:19PM +0200, lrotger wrote:
> I run the following query:
>
> update personal set closed_date = flt_date + 20
> from enr inner join personal p on enr.id = p.id_enr
> where (...columns from enr, columns from personal...)

I think your problem is that "personal" and "personal p" refer to
different instances of the same table. Use EXPLAIN to check how many
times "personal" appears in the resulting query.

Oh yeah, 7.2 will eat your data eventually, you have been warned.
Please upgrade to something newer.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Mon, Jun 26, 2006 at 01:00:19PM +0200, lrotger wrote:
>
>>I run the following query:
>>
>>update personal set closed_date = flt_date + 20
>>from enr inner join personal p on enr.id = p.id_enr
>>where (...columns from enr, columns from personal...)
>
>
> I think your problem is that "personal" and "personal p" refer to
> different instances of the same table. Use EXPLAIN to check how many
> times "personal" appears in the resulting query.

it worked, I thought I had to specify the table being updated in the
from clause too if only to have something to join to the other table, as
it turns out, it's not necessary. I hope the manual for 8.1.x explains
this or at least gives some examples of using this nonstandard from clause.

> Oh yeah, 7.2 will eat your data eventually, you have been warned.
> Please upgrade to something newer.

I'll have to allocate some time to read changelogs before I upgrade
across major versions.

thanks a lot,
Lucia