Обсуждение: self-referential UPDATE problem on 7.4

Hello !

I am trying to run this query in psql:

update clin.episode
set fk_patient = clin.health_issue.fk_patient
from clin.health_issue
where
    clin.episode.fk_patient is NULL and
    clin.episode.fk_health_issue = clin.health_issue.pk;

It returns "UPDATE 2" which is what I expect from the data.
However, the rows in question are not actually updated.

What I am trying to do:

- clin.episode has a nullable foreign key fk_health_issue to clin.health_issue.pk
- clin.health_issue has a not-nullable fk_patient
- clin.episode also has an fk_patient which is nullable

I want to transfer the value of clin.health_issue.fk_patient
to clin.episode.fk_patient to those clin.episodes for which
fk_patient is NULL.

I'm sure I am doing something wrong in a fairly basic way.

I tried with a subselect, too, but get the same result:

update clin.episode
set fk_patient = (
    select fk_patient from clin.health_issue chi where chi.pk=clin.episode.fk_health_issue
)
where fk_patient is NULL;

This is on 7.4.14 on Debian/Etch.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> I am trying to run this query in psql:

> update clin.episode
> set fk_patient = clin.health_issue.fk_patient
> from clin.health_issue
> where
>     clin.episode.fk_patient is NULL and
>     clin.episode.fk_health_issue = clin.health_issue.pk;

> It returns "UPDATE 2" which is what I expect from the data.
> However, the rows in question are not actually updated.

That seems very strange.  Could you perhaps have a BEFORE UPDATE trigger
that's changing the values back to null again?

            regards, tom lane

On Thu, Jan 04, 2007 at 11:36:35AM -0500, Tom Lane wrote:

> > update clin.episode
> > set fk_patient = clin.health_issue.fk_patient
> > from clin.health_issue
> > where
> >     clin.episode.fk_patient is NULL and
> >     clin.episode.fk_health_issue = clin.health_issue.pk;
>
> > It returns "UPDATE 2" which is what I expect from the data.
> > However, the rows in question are not actually updated.
>
> That seems very strange.  Could you perhaps have a BEFORE UPDATE trigger
> that's changing the values back to null again?
I do but I drop that one in the script before doing the
above update. I may have an old one hanging around, though,
I'll double-check.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Thu, Jan 04, 2007 at 06:37:23PM +0100, Karsten Hilbert wrote:

> > > It returns "UPDATE 2" which is what I expect from the data.
> > > However, the rows in question are not actually updated.
> >
> > That seems very strange.  Could you perhaps have a BEFORE UPDATE trigger
> > that's changing the values back to null again?
> I do but I drop that one in the script before doing the
> above update. I may have an old one hanging around, though,
> I'll double-check.
I was missing a schema qualification in a drop statement,
hence the culprit trigger function wasn't dropped properly.

Sometimes a hint helps.

Thanks Tom,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346