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

From: David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Exists subquery in an update ignores the effects of the update itself
Date: 2014-09-05 05:39:43
Message-ID: 1409895583958-5817890.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Jeff Janes wrote
> 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

You can probably solve the larger problem using deferred constraints.

http://www.postgresql.org/docs/devel/static/sql-set-constraints.html

Your stated problem can probably be solved using a CTE and a window
function. Write the cte query so that your duplicate-inducing values form a
partition and use row_number and order by to pick one of the items in each
partition as the first and only record to update. Then use that cte (or
subquery) to pick the rows on the table to actually apply the update to.

Basically an inline temp table.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Exists-subquery-in-an-update-ignores-the-effects-of-the-update-itself-tp5817885p5817890.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Nelson Green 2014-09-05 12:52:14 Re: Employee modeling question
Previous Message Jeff Janes 2014-09-05 05:12:53 Exists subquery in an update ignores the effects of the update itself