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

Jeff Janes <jeff(dot)janes(at)gmail(dot)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