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