Обсуждение: Exists subquery in an update ignores the effects of the update itself

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.

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.

Jeff Janes <jeff.janes@gmail.com> 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.
>
> 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?

update foo a
  set x = f2.x2
  from (
         select distinct on (substr(x,1,2))
             x, substr(x,1,2) as x2
           from foo
           order by substr(x,1,2), x
       ) f2
  where a.x = f2.x
    and not exists (select * from foo b where b.x = a.x)
;

The exists test is only there to cover any conflicting rows that
may exist before the statement starts; if you know there are none,
it could be omitted.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company