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
