Thread: 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.
Attachment
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