Exists subquery in an update ignores the effects of the update itself

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Exists subquery in an update ignores the effects of the update itself
Date: 2014-09-05 05:12:53
Message-ID: CAMkU=1xEObRpd5Ju8jsnBdqkxaWck7GH+9a1rfAVkMQ--fmzzg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to update some data in unique column. Some of the updates would
conflict if applied to eligible rows, and for now I want to skip those
updates, applying only one of a set of conflicting ones. I can use a not
exists subquery to detect when the new value would conflict with an
existing one, but it does not see the "existing" value if that value was
itself the result of an update in the same statement.

See the contrived example:

create table foo (x text unique);
insert into foo values ('aac'),('aad'),('aae');

update foo a set x=substr(x,1,2) where x!=substr(x,1,2)
and not exists (select 1 from foo b where b.x=substr(a.x,1,2));

ERROR: duplicate key value violates unique constraint "foo_x_key"
DETAIL: Key (x)=(aa) already exists.

Is there a way to phrase this in a single statement so it will do what I
want, updating one row and leaving two unchanged?

Or do I have to mess around with a temp table?

Thanks,

Jeff

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G Johnston 2014-09-05 05:39:43 Re: Exists subquery in an update ignores the effects of the update itself
Previous Message Vinayak 2014-09-05 05:00:31 CONCAT function