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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Exists subquery in an update ignores the effects of the update itself
Дата
Msg-id CAMkU=1xEObRpd5Ju8jsnBdqkxaWck7GH+9a1rfAVkMQ--fmzzg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Exists subquery in an update ignores the effects of the update itself
Re: Exists subquery in an update ignores the effects of the update itself
Список pgsql-general
Дерево обсуждения

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

В списке pgsql-general по дате отправления: