Jeff Janes <jeff.ja...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general