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